CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
kloop
kloop

Reputation: 4721

How do I get the following SQL query right to choose the top N results within a group?

I have the following SQL query in postgres:

  SELECT * FROM tableName ORDER BY a,b,c

I want within each of group of a,b -- only the top 10 results by the order of a,b,c will be chosen.

I tried things such as

  SELECT * FROM tableName ORDER BY a,b,c LIMIT 10

but that's of course not right -- it returns the top 10 results globally.

How can I choose the top 10 results within each group of a,b, ordered by a,b,c?

Upvotes: 1

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You use row_number():

select t.*
from (select t.*,
             row_number() over (partition by a, b order by c) as seqnum
      from tablename t
     ) t
where seqnum <= 10;

Upvotes: 3

Related Questions