Select the row with max value using row_number() or rank()

Question

I have data of following kind:

RowId   Name    Value
1       s1      12
22      s1      3
13      s1      4
10      s2      14
22      s2      5
3       s2      100

I want to have the following output:

RowId   Name    Value
1       s1      12
3       s2      100

I am currently using temp tables to get this in two step. I have been trying to use row_number() and rank() functions but have not been successful.

Can someone please help me with syntax as I feel row_number() and rank() will make it cleaner?

I changed the rowId to make it a general case

I am open to ideas better than row_number() and rank() if there are any.

Answer 1

If you use rank() you can get multiple results when a name has more than 1 row with the same max value. If that is what you are wanting, then switch row_number() to rank() in the following examples.

For the highest value per name (top 1 per group), using row_number()

select sub.RowId, sub.Name, sub.Value
from (
  select *
    , rn = row_number() over (
        partition by Name 
        order by Value desc
      )
  from t
  ) as sub
where sub.rn = 1

---

I can not say that there are any 'better' alternatives, but there are alternatives. Performance may vary.

cross apply version:

select distinct
    x.RowId
  , t.Name
  , x.Value
from t
  cross apply (
    select top 1
          *
      from t as i
      where i.Name = t.Name
      order by i.Value desc
    ) as x;

---

top with ties using row_number() version:

select top 1 with ties
    *
  from t
  order by 
    row_number() over (
      partition by Name
      order by Value desc
      )

---

This inner join version has the same issue as using rank() instead of row_number() in that you can get multiple results for the same name if a name has more than one row with the same max value.

inner join version:

select t.*
from t
  inner join (
    select MaxValue = max(value), Name
    from t
    group by Name
    ) as m
      on t.Name  = m.Name
     and t.Value = m.MaxValue;