CODEWITHSUNDEEP
mysqlsqlgroup-bygreatest-n-per-group
waitingkuo
waitingkuo

Reputation: 93964

In SQL, how to select the top 2 rows for each group

I have a table as following:

NAME    SCORE
-----------------
willy       1
willy       2
willy       3
zoe         4
zoe         5
zoe         6

Here's the sample

The aggregation function for group by only allow me to get the highest score for each name. I would like to make a query to get the highest 2 score for each name, how should I do?

My expected output is 

NAME    SCORE
-----------------
willy       2
willy       3
zoe         5
zoe         6

Upvotes: 27

Views: 86516

Answers (8)

Avinash Shastri
Avinash Shastri

Reputation: 131

If you don't mind having additional column then you can use the following code:

SELECT Name, Score, rank() over(partition by Name order by Score DESC) as rank
From Table
Having rank < 3;

Rank function provides rank for each partition, in your case it is name

Upvotes: 13

NguyenHuuThin
NguyenHuuThin

Reputation: 71

insert into test values('willy',1)
insert into test values('willy',2)
insert into test values('willy',3)
insert into test values('zoe',4)
insert into test values('zoe',5)
insert into test values('zoe',6)


;with temp_cte
as (
    select Name, Score,
       ROW_NUMBER() OVER (
          PARTITION BY Name
          ORDER BY Score desc
       ) row_num
    from test
)
select * from temp_cte
where row_num < 3

Upvotes: 5

Taryn
Taryn

Reputation: 247860

In MySQL, you can use user-defined variables to get a row number in each group:

select name, score
from
(
  SELECT name,
    score,
    (@row:=if(@prev=name, @row +1, if(@prev:= name, 1, 1))) rn
  FROM test123 t
  CROSS JOIN (select @row:=0, @prev:=null) c
  order by name, score desc 
) src
where rn <= 2
order by name, score;

See Demo

Upvotes: 11

Ronald Rivera
Ronald Rivera

Reputation: 1

Use this query.

select * from fruits 
where type = 'orange'  
order by price limit 2

Solution Here:
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Upvotes: -2

Jhamman Sharma
Jhamman Sharma

Reputation: 167

SELECT * FROM (   
    SELECT  VD.`cat_id` ,  
       @cat_count := IF( (@cat_id = VD.`cat_id`), @cat_count + 1, 1 ) AS 'DUMMY1', 
       @cat_id := VD.`cat_id` AS 'DUMMY2',
       @cat_count AS 'CAT_COUNT'   
     FROM videos VD   
     INNER JOIN categories CT ON CT.`cat_id` = VD.`cat_id`  
       ,(SELECT @cat_count :=1, @cat_id :=-1) AS CID  
     ORDER BY VD.`cat_id` ASC ) AS `CAT_DETAILS`
     WHERE `CAT_COUNT` < 4

------- STEP FOLLOW ----------  
1 . select * from ( 'FILTER_DATA_HERE' ) WHERE 'COLUMN_COUNT_CONDITION_HERE' 
2.  'FILTER_DATA_HERE'   
    1. pass 2 variable @cat_count=1 and  @cat_id = -1  
    2.  If (@cat_id "match" column_cat_id value)  
        Then  @cat_count = @cat_count + 1    
        ELSE @cat_count = 1      
    3. SET @cat_id = column_cat_id    

 3. 'COLUMN_COUNT_CONDITION_HERE'   
    1. count_column < count_number    

4. ' EXTRA THING '
   1. If you want to execute more than one statement inside " if stmt "
   2. IF(condition, stmt1 , stmt2 )
      1. stmt1 :- CONCAT(exp1, exp2, exp3) 
      2. stmt2 :- CONCAT(exp1, exp2, exp3) 
   3. Final "If" Stmt LIKE 
      1. IF ( condition , CONCAT(exp1, exp2, exp3) , CONCAT(exp1, exp2, exp3) )

Upvotes: 0

user2674659
user2674659

Reputation: 1

You can do somthething like this:

SET @num :=0, @name :='';   
SELECT name, score,
    @num := IF( @name= name, @num +1, 1 ) AS row_number,
    @name := name AS dummy
FROM test
GROUP BY name, score
HAVING row_number <=2

Upvotes: 0

devilcrab
devilcrab

Reputation: 139

For this you can do this-

http://www.sqlfiddle.com/#!2/ee665/4

but in order to get first 2 query, you should use a ID then run limit for ID like 0,2.

Upvotes: -1

John Woo
John Woo

Reputation: 263883

SELECT *
FROM   test s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    test  f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2

Upvotes: 25

Related Questions