How can I return the top 10 rows for each category?

Say I have a table named xyz. It has a column called state, in the political unit sense of the word.

I want the top 10 rows from each state. Do I use case so that one can see from which state the row is?

I am using SQL Server 2012.

The top 10 would be from FCFS, the order in which they are entered.

Columns are

FirstName, LastName, Address, city, state, zip, phone etc.

For example, I have rows in my table from states AL, CT, NY etc. I want the top 10 rows based on FCFS from each state.


So you want top 10 records per state by the date when record was inserted in DB. (I have assumed that you have date column for FCFS). You can use partition function for this.

from (
            ROW_NUMBER() over (partition by state order by date asc) as row, 
        from XYZ 
        where <where_condition>
        ) AS X
    X.row =< 10
order by <oder_by_clause>

(Assumed SQL Server for the query)

Check Ranking Functions, ROW_NUMBER for more details.

I hope this helps.

| improve this answer | |

In case of postgresql:

You didn't write which version you have from postgresql. You can use LATERAL JOINs (available from 9.3) or window functions.

Example for window function:

select *, row_number() over (partition by category_id order by [whatevercolumn]) as rownum 
 from records r
 join category c on c.id = r.category_id
 where c.id IN (1, 2, 3, 4) -- If you want to filter
) a
where rownum <= 10
order by category_id;

Example for LATERAL JOIN:

select r.* from category 
join lateral (
    select * from records 
    where category_id = category.id 
    order by [whatevercolumn]
    limit 10
) r on true
where category.id IN (1, 2, 3, 4) -- If you want to filter 
order by category.id;

The performance difference can be significant. For more info you can take a look at this benchmark: Group by limit per group in PostgreSQL

In SQL Server:

I'm not a user of SQL server but as far as I know it supports both. The syntax might be a bit different but the same idea applies.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.