How can I return top 10 rows from each state

Question

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.

score 1 · Answer 1 · answered Apr 25 '16 at 07:59

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.

Ketan · Accepted Answer · 2016-04-25T14:14:29.393

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.

select 
    <list_of_columns> 
from (
        select 
            ROW_NUMBER() over (partition by state order by date asc) as row, 
            <list_of_other_columns_from_XYZ> 
        from XYZ 
        where <where_condition>
        ) AS X
where 
    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.

How can I return top 10 rows from each state

2 Answers2