Selecting most recent field in SQL Server

[SQLLQS]

Normally this is what I would do:

SELECT LAST(FIELD_1),
LAST(FIELD_2)

FROM TABLE_NAME

GROUP BY SOME_REPEATING_FIELD

But SQL Server does not have the LAST aggregate function. Now I also have a DATE field. I need to get the MAX(DATE) and get the FIELD_1 and FIELD_2 in its same entry, while still GROUP BY SOME_REPEATING_FIELD.

Any help is much appreciated.

 

[carp]

Recommend you try a SQLServer forum rather than an ANSI SQL forum. You will probably get a better, more reliable answer in less time.

 

[PHV]

You may try something like this:
SELECT A.SOME_REPEATING_FIELD, A.FIELD_1, A.FIELD_2, A.DATE_FIELD
FROM DATE_FIELD A INNER JOIN (
SELECT SOME_REPEATING_FIELD, Max(DATE_FIELD) AS MaxDate
FROM TABLE_NAME GROUP BY SOME_REPEATING_FIELD
) B ON A.SOME_REPEATING_FIELD=B.SOME_REPEATING_FIELD AND A.DATE_FIELD=B.MaxDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244