How can I return top 10 rows 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.