I have an app in which values of various factors are stored in a table:

Date/Time Factor Value
01/01/2001 10:10.32 F21 2
01/01/2001 11:24.32 F21 1
01/01/2001 11:25.39 Q4 23
01/01/2001 11:29.12 Q4 65
01/01/2002 05:32.54 F21 3

I need to develop a view/sp to retrieve a recordset with only the most recent values for each unique factor. I am familiar with how to do this with access (GroupBy - First functionality) but I have not been able to figure this out in SQL Server 2000.

From the above sample table, I am looking for the following result:

Date/Time Factor Value
01/01/2001 11:29.12 Q4 65
01/01/2002 05:32.54 F21 3

Any assistance would be appreciated!

SELECT DT,FACTOR,VALUE FROM
(SELECT MAX(DATETIMEFIELD) AS DT,FACTOR FROM MYTABLE GROUP BY FACTOR) A
INNER JOIN MYTABLE B ON A.DT=B.DATETIMEFIELD AND A.FACTOR=B.FACTOR

Let me know how it works.
David

dhay1999

Thanks for the response, your suggestion worked like a charm!

I knew the solution would be straight forward... I guess I was staring at my screen too long to see the obvious!!

Thanks again!