19

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
John Woo
  • 238,432
  • 61
  • 456
  • 464
waitingkuo
  • 69,398
  • 23
  • 102
  • 115
  • If using Oracle SQL, see [*How do I limit the number of rows returned by an Oracle query after ordering?*](https://stackoverflow.com/q/470542/3357935) – Stevoisiak Nov 16 '20 at 17:30

8 Answers8

24
SELECT *
FROM   test s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    test  f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2
John Woo
  • 238,432
  • 61
  • 456
  • 464
  • 3
    but that may cause performance issue. is there any other quicker way to implement this query? –  Nov 06 '14 at 10:09
  • This does indeed cause pretty heavy performance problems (the sub-select is quadratic). – Kevin Sylvestre Dec 09 '14 at 20:30
  • 3
    This can be done linearly, see "MySQL Query to Get Top 2" here http://www.sqlines.com/mysql/how-to/get_top_n_each_group – mdubez Apr 14 '17 at 00:11
12

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

Taryn
  • 224,125
  • 52
  • 341
  • 389
  • Thank you for this solution, I'm still new in SQL. Hope that I can understand this one in the future :) – waitingkuo Apr 12 '13 at 11:13
  • @waitingkuo Unfortunately MySQL does not have windowing functions which would allow you to easily assign a row number to each rows in a group. – Taryn Apr 12 '13 at 11:17
  • @bluefeet thanks this is a very nice solution, it worked very fast even on 30k something rows, my earlier solution using joins was very slow – asm234 Dec 19 '13 at 06:03
  • Is this safe? MySQL states "the order of evaluation for expressions involving user variables is undefined." and doesn't that mean @prev:=name could be evaluated before your case statement and thus the case statement would artificially be true? Or am I missing something? See https://dev.mysql.com/doc/refman/5.5/en/user-variables.html – mdubez Apr 27 '17 at 04:12
8

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

Pavel
  • 3,332
  • 3
  • 21
  • 36
Avinash Shastri
  • 81
  • 1
  • 2
0

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.

devilcrab
  • 143
  • 2
  • 20
  • I'm afraid that it's not what I expect – waitingkuo Apr 12 '13 at 11:07
  • yea i was just giving method you can do it simply, if you have maintained ID for each row (primary key) it will work more and you will have more functions in hand. In the way you need its long code and also , it will be harder for you to use anything else over it in future. – devilcrab Apr 12 '13 at 11:10
0

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
user2674659
  • 1
0
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) )
Jhamman Sharma
  • 61
  • 1
  • 2
0
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
NguyenHuuThin
  • 11
  • 2
  • Good first answer! Please test your code before posting (missing semicolons after inserts). Explain it (using comments) as well as used concepts, e.g. `WITH` and [Common Table Expersion](https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions). – hc_dev Dec 19 '19 at 20:54
-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/

Ravi Matani
  • 794
  • 1
  • 7
  • 21
Ronald Rivera
  • 1
  • 1