SQLTeam.com | Weblogs | Forums

SQL Query

Hi, I am in need of some expert help please. I have a query that returns a list. The below shows only the most recent LastLogin rows but there are many more.

Name LastLogin Time Device
Jon Smith 18/08/2019 21:59:52 2016 *
James Browne 18/08/2019 20:02:12 2016
James Browne 18/08/2019 18:41:38 2016
James Browne 18/08/2019 18:28:40 2016
James Browne 18/08/2019 13:57:56 2410 *
James Browne 18/08/2019 13:55:56 2016
....

It is date/time order descending. I would like to only return two rows from this list which have the asterisk against them. They are the two most recent LastLogins by Name and Device. Is there a simple way to achieve this in SQL?

Many thanks in advance.
Mat

SELECT TOP(2) Name, LastLoginTime
FROM ...
ORDER BY LastLoginTime;

Hi and thanks for your quick reply. I can't use Top(2) as this will return

|Name|LastLogin|Time|Device|
|Jon Smith|18/08/2019|21:59:52|2016 *|
|James Browne|18/08/2019|20:02:12|2016|

but I need the
James Browne 18/08/2019 13:57:56 2410 *
I also have dates going back in time so I can't order by just the LastLoginTime I have to have date and time.

Any other suggestions would be welcome.

If you will take the time to post sample data in the form of create table, insert statements and desired result using the sample data you provide, I or someone else will show you how .

SELECT Name, LastLogin, Time, Device
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Device 
        ORDER BY LastLogin DESC, Time DESC) AS row_num
    FROM dbo.table_name
) AS query1
WHERE row_num = 1
ORDER BY Device
1 Like

Hi Scott,
That's amazing! Thank you very much it works perfectly.
Mat

You're welcome! Glad it helped.

Btw, note that the "*" in the inner query does not hurt performance. SQL is sophisticated enough to only pull the columns you're using in the outer query. This makes it really easy to add a column to the outer query later, since you don't have to remember to add it to the inner query as well.

1 Like