SELECT clause with LAST function causing problems

[antifashionpimp]

Hello all,


I have the following data, that was queried and sorted to columns PROBLEM_ID and then by STATUSDATE (ascending):

STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10________12/04/2005___1234
40________15/05/2005___1234
10________11/11/2004___1235
50________15/12/2004___1235
70________20/01/2005___1235

I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:

SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“

I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!

Kind Regards,

Jean

 

[DocNice]

Can you show the syntax of the query that gives you an error?

 

[mresann]

Use a subquery to find the max date

SELECT PROBLEM_ID, STATUS_ID
FROM qryFirst AS qryFirst_A
WHERE STATUSDATE =
(SELECT TOP 1 (qryFirst.STATUSDATE)
FROM qryFIRST
ORDER BY STATUSDATE DESC; )

 

[FoFa]

Which STATUS_DATE? Since you could have multiple records making up your results, it is basically saying, Which One? Lets assume MAX, then it would be like this:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS, max(qryFirst.STATUSDATE) AS LastStatusDate
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

 

[mresann]

Which STATUS_DATE? Since you could have multiple records making up your results, it is basically saying, Which One? Lets assume MAX, then it would be like this:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS, max(qryFirst.STATUSDATE) AS LastStatusDate
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;

This is what pimp said:

I now want another query which returns the row which is always the latest STATUSDATE.

Your query returns multiple rows, depending on how many different problem_Id's there are. His request was for "the row", meaning single row.

 

[FoFa]

Well then, since Latest can be translated to MAX (since it is a date), and the original query was already grouping by ID, then that query should work without the extra overhead of a subquery.

 

[antifashionpimp]

Thanks guys,

I used the MAX function in the end, and it works. Much more clearer than the LAST function

Regards,

J

 

[Pat Hartman]

If you look up "last function" in help you will (hopefully) understand why last and first don't work the way people expect them to work. Almost invariabley when someone says they want the "last" of something, they really mean "latest" and that is best accomplished with the max function.