asked on Is it possible to, with 1 SELECT get the latest added row PER USER (in field USERID)
I have a SQL db which is populated by a LoginScript.
Every row represents an unique Login and have several fields like ID,TIME,USERID,CLIENT, etc.
What I wonder is if I can, with a single SELECT get only 1 row (or few fields) per user, the LATEST, from lets say 1 month back?
Every row represents an unique Login and have several fields like ID,TIME,USERID,CLIENT, etc.
What I wonder is if I can, with a single SELECT get only 1 row (or few fields) per user, the LATEST, from lets say 1 month back?
SQL
Last Comment
Richard Skogsbergh
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
A happy man is closing his first question. Thank you!!
Well done. Congratulations.
Just a small point but for the date range you can omit the time in the date literals, and you should use >= for the lower date:
WHERE TIME1>='2018-03-01' AND TIME1<'2018-03-17'
Although it may seem odd the safest date literals in SQL Server omit the dashes too
WHERE TIME1>='20180301' AND TIME1<'20180317'
Cheers.
Just a small point but for the date range you can omit the time in the date literals, and you should use >= for the lower date:
WHERE TIME1>='2018-03-01' AND TIME1<'2018-03-17'
Although it may seem odd the safest date literals in SQL Server omit the dashes too
WHERE TIME1>='20180301' AND TIME1<'20180317'
Cheers.
ASKER
Ok, thanks again! :-D
It is a SQL Server 2008
I am not (yet) familiar with "combings" like this but I'm on my way ;)
Your answer seems to work exactly the way I want!! :)
I removed and added some to get what I really needed and now I am really happy!! :)
SELECT *
FROM (
SELECT
TIME1,USERID,FIELD1,FIELD2
,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIME1 DESC) AS RN
FROM TABLE) D
WHERE RN = 1 AND TIME1>'2018-03-01 00:00:00'
ORDER BY TIME1 DESC
Thank you very much!!!!!