i need to get the value of the max(date) of a table per person

I got a table SITUATION with a userID, a date and a situation.
Unique number
UserID
SitDate
Situation Int
I Need to get the last situation of the UserID
I'm stuck there with that query and it come out with the last for each
situation I only need the last situation.
Query:
SELECT DISTINCT MAX(DISTINCT SITUATION.SitDate) AS DateSit,
SITUATION.UserID, SITUATION.Situation
FROM SITUATION
GROUP BY SITUATION.PrevenuID,SITUATION.Situation
ORDER BY SITUATION.PrevenuID
Thanks for your help
EmmanuelJust a guess, and assumes that UserID/SitDate combo is "unique enough" (e.g.
includes time information). Please see http://www.aspfaq.com/5006 for help
on providing clear and complete requirements.
SELECT s.[Unique number],
s.UserID,
s.SitDate,
s.Situation
FROM
Situation s
INNER JOIN
(
SELECT UserID,
MAX(SitDate)
FROM Situation
GROUP BY UserID
) sub
ON
sub.UserID = s.UserID
AND s.SiteDate = sub.SiteDate;
"Emmanuel Vandal" <evandal@.videotron.com> wrote in message
news:Ft4Ef.46134$rX.677832@.weber.videotron.net...
>I got a table SITUATION with a userID, a date and a situation.
> Unique number
> UserID
> SitDate
> Situation Int
> I Need to get the last situation of the UserID
> I'm stuck there with that query and it come out with the last for each
> situation I only need the last situation.
> Query:
> SELECT DISTINCT MAX(DISTINCT SITUATION.SitDate) AS DateSit,
> SITUATION.UserID, SITUATION.Situation
> FROM SITUATION
> GROUP BY SITUATION.PrevenuID,SITUATION.Situation
> ORDER BY SITUATION.PrevenuID
> Thanks for your help
>
> Emmanuel
>|||Thank you, The "Unique enough" term just solve my problem, I generate many
data with a code that put the same date everywhere so the query was good but
the data not.
Emmanuel
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le
message de news: ujhUna0JGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Just a guess, and assumes that UserID/SitDate combo is "unique enough"
> (e.g. includes time information). Please see http://www.aspfaq.com/5006
> for help on providing clear and complete requirements.
>
> SELECT s.[Unique number],
> s.UserID,
> s.SitDate,
> s.Situation
> FROM
> Situation s
> INNER JOIN
> (
> SELECT UserID,
> MAX(SitDate)
> FROM Situation
> GROUP BY UserID
> ) sub
> ON
> sub.UserID = s.UserID
> AND s.SiteDate = sub.SiteDate;
>
> "Emmanuel Vandal" <evandal@.videotron.com> wrote in message
> news:Ft4Ef.46134$rX.677832@.weber.videotron.net...
>