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