Get the max value one column but group on another

In SQL Server I am trying to figure out how to get an entire row for the latest record of a user. I have a table where it’s basically a log of all users who do an action and a date time. So for example

Basically I would want to query this table in order to return just the latest datetime rows for each unique user like this:

So far I tried doing this

select u.userid, u.action, u.datetime
from user_logs u
inner join (
    select userid, max(datetime) as datetime 
    from user_logs
    group by userid
) tmp on tmp.userid = u.userid and tmp.datetime = u.datetime
order by u.userid

However this seems to still give me multiple rows with duplicate userids, different action statuses, but with all the same datetime now

>Solution :

Window function ROW_NUMBER() to the rescue.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (userId INT, action  VARCHAR(10), actionTime DATETIME)
INSERT INTO @tbl (userId, action, actionTime) VALUES
(1, 'jump', '2022-01-01 07:50:00.000'),
(2, 'run' , '2022-01-02 07:50:00.000'),
(3, 'walk', '2022-01-01 07:50:00.000'),
(3, 'run' , '2022-01-04 07:50:00.000'),
(4, 'jump', '2022-01-01 07:50:00.000'),
(2, 'walk', '2022-01-01 07:50:00.000'),
(1, 'walk', '2022-01-01 01:50:00.000'),
(1, 'walk', '2022-01-03 04:50:00.000'),
(4, 'run' , '2022-01-03 07:50:00.000');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT * 
        , ROW_NUMBER() OVER (PARTITION BY userId ORDER BY actionTime DESC) AS seq
    FROM @tbl
)
SELECT * 
FROM rs
WHERE seq = 1;

Output

+--------+--------+-------------------------+-----+
| userId | action |       actionTime        | seq |
+--------+--------+-------------------------+-----+
|      1 | walk   | 2022-01-03 04:50:00.000 |   1 |
|      2 | run    | 2022-01-02 07:50:00.000 |   1 |
|      3 | run    | 2022-01-04 07:50:00.000 |   1 |
|      4 | run    | 2022-01-03 07:50:00.000 |   1 |
+--------+--------+-------------------------+-----+