Select latest row for multiple records

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

SQL Server Development (2000)

Select latest row for multiple records

Author

Topic

djsjbjcj
Starting Member

4 Posts

Posted - 2007-10-29 : 14:50:57

I have a table that records users activity. The columns are:
USERID, EVENTDATE, EVENTTYPE

I need a query to get the latest record for each USERID, and the eventtype for that record. How can I do this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-29 : 15:20:26

Use corelated query.


SELECT U.*
FROM Users U
JOIN (Select MAX(EventDate) as MDate, UserID
	FROM Users U2
	GROUP BY Userid)
	) U3 ON U.Userid U3.UserId AND U.EventDate = U3.MDate

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 15:34:17

Beware if there are potentially multiple [eventtype] for the exact same "latest record" for a given [USERID]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources