A pesky recordset

I have the following User_Log table:
ID User Location Use_Date
1 1234 RR 1/5/05
2 1234 KT 1/10/05
3 1234 ZF 1/20/05
4 1259 ZF 1/13/05
5 1259 KT 1/19/05
I need a T-SQL statement that will get me the two user records with the most
recent Use_date. In this case it would be the fields with the IDs of 3 and
5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
date is 1/19/05. If I do a
SELECT User, max(Service Date)
FROM User_Log
GROUP BY User
I lose the location. If I include the Location in my query, I obviously get
a record for each location. So how do I get the best of both worlds;
Grouping on the user to get the max date but not losing the location of the
record that qualifies in the max statement?
Thanks,
JonSELECT user, location, use_date
FROM User_Log AS L
WHERE use_date =
(SELECT MAX(use_date)
FROM User_Log
WHERE user = L.user)
David Portas
SQL Server MVP
--|||SELECT User, Location, Use_Date
FROM User_log ul
WHERE Used_Date = (SELECT MAX(Use_Date) FROM User_log ul2
WHERE ul.User = ul2.User)
or
SELECT ul.User, ul.Location, ul.Use_Date
FROM User_log ul
INNER JOIN
(SELECT User, MAX(Use_Date) AS max_use_date FROM User_log ) ul2
ON ul.User = ul2.User AND ul.Use_date = ul2.max_use_date
Although both of them assume that the combination of User and Use_date is
unique.
--
Jacco Schalkwijk
SQL Server MVP
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:uYQavE7DFHA.3728@.TK2MSFTNGP14.phx.gbl...
>I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the
> most recent Use_date. In this case it would be the fields with the IDs of
> 3 and 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent
> service date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously
> get a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of
> the record that qualifies in the max statement?
> Thanks,
> Jon
>|||SELECT TOP 2
Id,
User,
Location,
Use_Date
FROM
Users
ORDER BY
Use_Date DESC
"Jon Westmore" wrote:

> I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the mo
st
> recent Use_date. In this case it would be the fields with the IDs of 3 an
d
> 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
> date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously g
et
> a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of th
e
> record that qualifies in the max statement?
> Thanks,
> Jon
>
>