Most recent item 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

SQL Server Development (2000)

Most recent item for each user

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-30 : 09:27:42

Eric writes "I have 2 tables, one of users (keyed by UserID), and another of things. Things were created by users, and the date they were created is stored in CreatedDate. I'd like to get a list of ThingIDs for the most recent Thing for each UserID. Some users might not have created a Thing yet, and I don't want them returned (but it won't hurt if they are), also, I don't want to rely on the CreatedDate to uniquely identify the ThingID. I'm using SQL 2000, but anything that can help....."

izaltsman
A custom title

1139 Posts

Posted - 2001-11-30 : 10:43:52

Try this:


select things.userid, MIN(things.thingid)
from things inner join (SELECT userid, MAX(createdate) maxdate FROM things) as a 
			ON things.userid = a.userid AND thing.createdate = a.maxdate
group by things.userid

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-30 : 10:54:14

Do you (Eric, that is) mean something like this?
Include latest ties for each user:


SELECT UserID, ThingID
FROM UserThings a
WHERE CreatedDate = (
  SELECT MAX(CreatedDate)
  FROM UserThings b
  WHERE a.UserID = b.UserID)

Pick one latest for each user:


SELECT UserID, MAX(ThingID)
FROM UserThings a
WHERE CreatedDate = (
  SELECT MAX(CreatedDate)
  FROM UserThings b
  WHERE a.UserID = b.UserID)
GROUP BY UserID

Edited by - Arnold Fribble on 11/30/2001 10:56:51

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-30 : 11:11:40

Putting back in the missing "GROUP BY userid" in izaltsman's subquery, it's perhaps worth noting that SQL Server 2000 will (probably) generate the same query plan for both that query and the second of mine. At least, it does on Northwind, changing:
(User)Things -> [Order Details]
UserID -> OrderID
ThingID -> ProductID
CreatedDate -> Quantity

Edited by - Arnold Fribble on 11/30/2001 11:12:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources