CODEWITHSUNDEEP
sqlsql-serversql-server-2008greatest-n-per-group
mjallday
mjallday

Reputation: 10072

Finding top n for each unique row

I'm trying to get the top N records for each unique row of data in a table (I'm grouping on columns b,c and d, column a is the unique identifier and column e is the score of which i want the top 1 in this case).

a    b    c    d    e
2    38   NULL NULL 141
1    38   NULL NULL 10
1    38   1    NULL 10
2    38   1    NULL 1
1    38   1    8    10
2    38   1    8    1
2    38   16   NULL 140
2    38   16   12   140

e.g. from this data i would like to find the following rows:

a    b    c    d    e
2    38   NULL NULL 141
1    38   1    NULL 10
1    38   1    8    10
2    38   16   NULL 140
2    38   16   12   140

can someone please point me in the right direction to solve this?

Upvotes: 0

Views: 259

Answers (3)

Aishwar
Aishwar

Reputation: 9714

I believe this will do what you said (extending the idea from here):

select b,c,d,e, 
rank() over 
(partition by b,c,d order by e desc) "rank" 
from t1 where rank < 5

Cheers.

Upvotes: 0

Steve Kass
Steve Kass

Reputation: 7184

Your example doesn't show, and you don't explain how you determine which row is the "top" one, so I've put ?????? in the query where you need to provide a ranking column, such as

a desc

for example. In any case, this is exactly what the analytic functions in SQL Server 2005 and later are for.

declare @howmany int = 3;
with TRanked (a,b,c,d,e,rk) as (
  select
    a,b,c,d,e,
    rank() over (
      partition by b,c,d
      order by ???????
    )
  from T
)
  select a,b,c,d,e
  from TRanked
  where rk <= @howmany;

Upvotes: 1

Peter Radocchia
Peter Radocchia

Reputation: 11007

The nulls are a pain, but something like this:

select * from table1 t1
where a in (
  select top 1 a from table1 t2
  where (t1.b = t2.b or (t1.b is null and t2.b is null))
    and (t1.c = t2.c or (t1.c is null and t2.c is null))
    and (t1.d = t2.d or (t1.d is null and t2.d is null))
  order by e desc
  )

or better yet:

select * from (
  select *, seqno = row_number() over (partition by b, c, d order by e desc)
  from table1
  ) a
where seqno = 1

Upvotes: 0

Related Questions