Finding row with max values in two groups

Question

I use SQL Server 2012, I have a following table:
id, name, surname, timestamp, type

type has two possible values: 1 and 2.

Now, I would like to find two rows - for each group (1 and 2) row with maximal value in particular type.

The problem is that I would like to find both name and surname. I can do it with SELECT TOP 1 - WHERE ORDER BY - UNION approach, but I would like to find antother, better idea. Can you help me ?

Can you give some sample data and the desired output? Will make it easier to give a more accurate answer. — Keith, Oct 17 '17 at 18:23
variation of https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/19433107#19433107 I believe — Twelfth, Oct 17 '17 at 18:29

score 0 · Answer 1 · answered Oct 17 '17 at 18:31

This sounds like you want the most recent for each row, for each type. If that's the case, here is a way with row_number()

with cte as(
select
    id
    ,name
    ,surname
    ,timestamp
    ,type
    RN = row_number() over (partition by id,type order by timestamp desc))

select *
from cte
where RN = 1

Finding row with max values in two groups

1 Answers1