En SQL, cómo seleccionar las 2 filas superiores para cada grupo

Tengo una table como la siguiente:

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

Aquí está la muestra

La function de agregación para el group by solo me permite get la puntuación más alta para cada name . Me gustaría hacer una consulta para get el puntaje más alto 2 para cada name , ¿cómo debo hacer?

Mi resultado esperado es

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

 SELECT * FROM test s WHERE ( SELECT COUNT(*) FROM test f WHERE f.name = s.name AND f.score >= s.score ) <= 2 
  • Demostración de SQLFiddle

En MySQL, puede usar variables definidas por el usuario para get un número de fila en cada grupo:

 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; 

Ver demostración

Si no te importa tener una columna adicional, puedes usar el siguiente código:

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

La function de range proporciona un range para cada partición, en su caso es el nombre

Para esto puedes hacer esto-

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

pero para get las primeras 2 consultas, debe usar una ID y luego ejecutar el límite para ID como 0,2.

Puedes hacer algo como esto:

 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 
 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) ) 

Usa esta consulta

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

Solución aquí:
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/