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?
LVL 1
Richard SkogsberghCitrix TechieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
What database is it for?  eg
SQL Server?
MySQL?

"SQL db" doesn't make it clear I'm afraid.

==

Try using row_number() over()
SELECT *
FROM (
    SELECT
           ID,TIME,USERID,CLIENT
        , ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIME DESC) AS RN
    FROM YOURTABLE) D
WHERE RN = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard SkogsberghCitrix TechieAuthor Commented:
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!!!!!
0
Richard SkogsberghCitrix TechieAuthor Commented:
Need to correct my own adding to your code here..
I added a Date so that I only want to show result added to DB after '2018-03-01 00:00:00' .
I was a little suspicious because the query took so long time, just like if I searched to whole DB (from 2016-xx)

Then I added the Date-limit to another place in the Query and then speed was down from like 8-10 seconds to just under 1 sec!!!
So, here is the Query I use that gives me exactly what I want, FAST!! :-)

SELECT *
 FROM (
    SELECT
       TIME1,USERID,FIELD1,FIELD2
       ,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIME1 DESC) AS RN
    FROM TABLE WHERE TIME1>'2018-03-01 00:00:00' AND TIME1<'2018-03-17 00:00:00') D
 WHERE RN = 1
 ORDER BY TIME1 DESC

Again, 10.000 thanks to you for this GREAT answer!!
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Richard SkogsberghCitrix TechieAuthor Commented:
A happy man is closing his first question. Thank you!!
0
PortletPaulfreelancerCommented:
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.
1
Richard SkogsberghCitrix TechieAuthor Commented:
Ok, thanks again! :-D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.