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

Answer 1

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