How can I return top 10 rows from each state 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.
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 &lt;= 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.