Link to home
Start Free TrialLog in
Avatar of Richard Skogsbergh
Richard SkogsberghFlag for Sweden

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?
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Skogsbergh

ASKER

Hi

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!!!!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Ok, thanks again! :-D