CODEWITHSUNDEEP
sql-server
Zanam
Zanam

Reputation: 4807

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

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.

Upvotes: 3

Views: 25016

Answers (3)

SqlZim
SqlZim

Reputation: 38023

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;

Upvotes: 7

Siyual
Siyual

Reputation: 16917

If you really want to use ROW_NUMBER() you can do it this way:

With Cte As
(
    Select  *,
            Row_Number() Over (Partition By Name Order By Value Desc) RN
    From    YourTable
)
Select  RowId, Name, Value
From    Cte
Where   RN = 1;

Upvotes: 5

manderson
manderson

Reputation: 891

Unless I'm missing something... Why use row_number() or rank?

select rowid, name, max(value) as value
from table
group by rowid, name

Upvotes: 0

Related Questions