How to get max row in a table with 3 columns

Question

I'm running Microsoft SQL Server 2014 - 12.0.4213.0 (X64).

(Apologies - I'm a newbie and I know I'm running an old version)

I have the following table:

ID	Name	Time
1	Finished	2022-07-13 17:09:48.0000000
1	Start	2022-07-13 17:00:48.0000000
2	Clean	2022-07-13 15:09:48.0000000
2	Waiting	2022-07-13 17:34:48.0000000
2	Clean	2022-07-13 12:09:48.0000000
3	Start	2022-07-12 18:09:48.0000000
3	Middle	2022-07-12 14:09:48.0000000
3	Middle	2022-06-13 17:09:48.0000000

I want to return a group that will show the max time for each ID number, but also return the Name value of that max row.

I can do a

SELECT
    ID, MAX(Time)
FROM
    ...
WHERE
    ...
GROUP BY
    (ID)

but I need to pull in the Name column as well. I just want one row per ID returning the max time for that ID, and the Name associated with that Time & ID number

Any help would be great thank you

score 2 · Accepted Answer · answered Jul 19 '22 at 15:28

This kind of thing has been asked and answered so many times, but finding the right search term can be challenging. Here is how you can tackle this with your sample data.

declare @Something table
(
    ID int
    , Name varchar(20)
    , Time datetime2
)

insert @Something values
(1, 'Finished', '2022-07-13 17:09:48.0000000')
, (1, 'Start', '2022-07-13 17:00:48.0000000')
, (2, 'Clean', '2022-07-13 15:09:48.0000000')
, (2, 'Waiting', '2022-07-13 17:34:48.0000000')
, (2, 'Clean', '2022-07-13 12:09:48.0000000')
, (3, 'Start', '2022-07-12 18:09:48.0000000')
, (3, 'Middle', '2022-07-12 14:09:48.0000000')
, (3, 'Middle', '2022-06-13 17:09:48.0000000')

select ID
    , Name
    , Time
from
(
    select *
        , RowNum = ROW_NUMBER()over(partition by s.ID order by s.Time desc)
    from @Something s
) x
where x.RowNum = 1

thank you Sean. yes i tried searching but had no luck. yours worked for me thank you again — Jdbfkfo, Jul 19 '22 at 15:35

score 2 · Answer 2 · answered Jul 19 '22 at 15:36

2

Just another option (a nudge less performant)

Select Top 1 with ties *
 From  YourTable 
 Order By row_number() over (partition by ID order by Time desc)

answered Jul 19 '22 at 15:36

John Cappelletti

79,615
7
44
66

@Jdbfkfo Small tables are fine. Larger tables Sean's approach would be notably more performant. – John Cappelletti Jul 19 '22 at 15:54

Sayan Bhattacharya · Answer 3 · 2022-07-19T15:48:45.407

0

This can also work

select * from table
where time in (select max(time) from table group by id )

But other's answers seem more efficient.

I have not tested this, if it's wrong then will delete the answer.

edited Jul 19 '22 at 15:48

answered Jul 19 '22 at 15:43

Sayan Bhattacharya

1,365
1
4
14

2

This would work if all the values for time are unique. But if there are more than 1 row where the value for time is the same for the same id this would produce duplicates. That is not very likely but something to consider. – Sean Lange Jul 19 '22 at 15:50
Thanks for clarifying, It is a possible scenario and did not occur to me :( . – Sayan Bhattacharya Jul 19 '22 at 15:55

How to get max row in a table with 3 columns

3 Answers3