How to get max row in a table with 3 columns

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:

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

>Solution :

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