Get the max value one column but group on another

Question

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

userId	action	datetime
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

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

userId	action	datetime
1	walk	2022-01-03 04:50:00.000
2	run	2022-01-02 07:50:00.000
3	run	2022-01-04 07:50:00.000
4	run	2022-01-03 07:50:00.000

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

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Feb 15 '22 at 22:20

score 1 · Accepted Answer · answered Feb 15 '22 at 22:28

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 |
+--------+--------+-------------------------+-----+

score 0 · Answer 2 · answered Feb 15 '22 at 22:29

0

Typical case for window functions.

select userid, action, datetime
  from (select u.*,
               row_number() over (partition by userid order by datetime desc) rn
          from user_logs u) _
 where rn = 1

answered Feb 15 '22 at 22:29

Kombajn zbożowy

8,755
3
28
60

score 0 · Answer 3 · answered Feb 15 '22 at 22:30

0

You can use ROW_NUMBER() to find the latest row for each user.

For example, you can do:

select *
from (
  select *,
    row_number() over(partition by userId order by datetime desc) as rn
  from user_logs
) x
where rn = 1

answered Feb 15 '22 at 22:30

The Impaler

45,731
9
39
76

Get the max value one column but group on another

3 Answers3