Select top 2 sales in each class SQL

Question

I have the table which I want select only top 2 sales in each class, the result is

Sophia   A 40
Jennifer A 15
Greg     B 50
Jeff     B 20
Stella   B 20

score 4 · Answer 1 · answered Sep 02 '18 at 11:30

4

You could use RANK:

SELECT *
FROM (SELECT *, RANK() OVER(PARTITION BY class ORDER BY sales DESC) AS rnk
      FROM tab) sub
WHERE rnk <=2;

answered Sep 02 '18 at 11:30

Lukasz Szozda

Yaa, it works but Stella and Jeff are same at sales only one shows up – KEVINBOSS Sep 02 '18 at 11:33
@KEVINBOSS Please check again. RANK() will give the same value for tie. – Lukasz Szozda Sep 02 '18 at 11:34
I just checked again and I only got 4 rows as it should be 5 – KEVINBOSS Sep 02 '18 at 11:37

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-02T14:24:05.983

1

window function dense_rank also fulfil expected result condition of sample data

 select * from
    (select * , dense_rank() over(partition by class order by scores DESC ) as rn 
     from tablea
     ) t where rn<=2

edited Sep 02 '18 at 14:24

answered Sep 02 '18 at 14:17

@KEVINBOSS https://stackoverflow.com/help/accepted-answer there is a option accept answer click on that answer will be accept – Zaynul Abadin Tuhin Sep 03 '18 at 05:51
cool in this case dense_rank is better than rank () thank you :) – KEVINBOSS Sep 03 '18 at 06:20

2 Answers2