Get Latest Results for each User

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get Latest Results for each User

Author

Topic

jgallen23
Starting Member

17 Posts

Posted - 2006-07-19 : 14:09:58

I have a table that has ID, Note and Date. I need to write a query that would show the latest notes for each date.

Example would be ID = 3 has a note of "blah" on 07/16 and "blah2" on 07/19 and ID = 2 has a note of "test1" on 07/15 and "test2" on 07/18, I would want it to return

2 test2 07/18
3 blah2 07/19

Could somebody give me some tips on how to write this sql query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-19 : 14:15:17

To get the max date for each ID, you do this:

SELECT [ID], MAX([Date]) AS [Date]
FROM YourTable
GROUP BY [ID]

In order to get the note associated with that date, we use your table and join it to the above query as a derived table.


SELECT [ID], Note, [Date]
FROM YourTable y
INNER JOIN
(
	SELECT [ID], MAX([Date]) AS [Date]
	FROM YourTable
	GROUP BY [ID]
) t
ON y.[ID] = t.[ID] AND y.[Date] = t.[Date]

Tara Kizer

jgallen23
Starting Member

17 Posts

Posted - 2006-07-19 : 14:33:35

thank you for the quick response! That worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources