Getting the "most recent" record for a range

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)

Getting the "most recent" record for a range

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-22 : 10:12:42

I've split this question to a new discussion as it is tougher.

The problem of "most recent" is split accross two tables: Users and Visitations. The SELECT is targeting all users meeting a criteria like (state='Nevada').

The following select returns records of users in Nevada and "all their visitations"

SELECT * from Users inner join Visitations
on Users.userid=Visitations.Userid
where users.state='Nevada'

Any thoughts on how to select the "most recent" visitation records?

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 10:17:09

Actually, the version you had in your other post was very close:

SELECT * FROM Users U INNER JOIN Visitations V ON U.UserID=V.UserID
INNER JOIN
(SELECT UserID, Max(VisitDate) AS MaxDate FROM Visitations GROUP BY UserID) M
ON U.UserID=M.UserID AND V.VisitDate=M.MaxDate
WHERE U.State='Nevada'

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-22 : 11:01:22

Rob,

Perfect!

Thanks,

SamC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources