SQL Query to Get Most Recent Row for Each Instance of a Given Key

SQL query to get most recent row for each instance of a given key

Try this:

Select u.[username]
      ,u.[ip]
      ,q.[time_stamp]
From [users] As u
Inner Join (
    Select [username]
          ,max(time_stamp) as [time_stamp]
    From [users]
    Group By [username]) As [q]
On u.username = q.username
And u.time_stamp = q.time_stamp

T-SQL Query: Select only the most recent row (for each computer name)

You can do this by using RowNumber which gives a row number to every row. Since you want to give row number starting from 1 for each computerName based on TimeStamp, we need to partition it by Computername

select * from (
select *, ROW_NUMBER() over (partition by ComputerName order by Timestamp desc) as rno 
from ComputerStatus
) as T where rno = 1

MSDN

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

Get most recent row for given ID

Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id.

SELECT 
 id, 
 MAX(signin) AS most_recent_signin
FROM tbl
GROUP BY id

To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id.

SELECT 
  tbl.id,
  signin,
  signout
FROM tbl
  INNER JOIN (
    SELECT id, MAX(signin) AS maxsign FROM tbl GROUP BY id
  ) ms ON tbl.id = ms.id AND signin = maxsign
WHERE tbl.id=1

how do I query sql for a latest record date for each user

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

Get the most recent and not null row

You can use FIRST_VALUE() window function:

SELECT DISTINCT id, 
       FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY attribute1 IS NULL, timestamp DESC) attribute1,
       FIRST_VALUE(attribute2) OVER (PARTITION BY id ORDER BY attribute2 IS NULL, timestamp DESC) attribute2
FROM tablename;

See the demo.

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

---

Related Topics