SQL Query

1

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

2

add a where condition to your query with the column that has * value .

where Device like '%*'

3

You can use row_number function to identify the current row.

  With latestLogin
    As (
Select Name
     , LastLogin
     , Time
     , Device
     , rn = row_number() over(Partition By Name, Device Order By LastLogin desc, Time desc)
       )
Select *
  From latestLogin
 Where rn = 1;