Search code examples
sqlsql-servergreatest-n-per-group

How to achieve this in SQL

I have this table:

enter image description here

I want to GROUP BY category and then select only top 2 product's Name, Category, and Rating.

So final output should be like this:

Id      Name      Category      Rating
1       Iphone    Electronics   5
7       MI        Electronics   5
4       Top       Fashion       5
9       Shoes     Fashion       5
10      Coffe     Food          5
11      jAM       Food          4

I tried to solve via my way but still no luck. I want the top 2 result from every category based on rating if one category have two products of the same rating like 5 then result should show both.

I am new on stackOverFlow thats my first question i doesn't know much about the manners and rules but i will learn.

Sorry if i breaks any rule.

Solution

  • You can do something like this:

    select * 
from (
  select *, 
    rank() over (partition by Category order by Rating desc) as Rank
  from yourtable
) X
where Rank <= 2

    You might want to look the differences of rank vs dense_rank in case you have the same rating.