kloop kloop - 1 year ago 64
SQL Question

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?

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download