Obtenha os primeiros n registros para cada grupo de resultados agrupados

O exemplo a seguir é o mais simples possível, embora qualquer solução deva ser capaz de escalar para muitos resultados n principais:

Dada uma tabela como essa abaixo, com colunas de pessoa, grupo e idade, como você obteria as duas pessoas mais velhas em cada grupo? (Os agrupamentos dentro dos grupos não devem render mais resultados, mas dar os primeiros 2 em ordem alfabética)

 + -------- + ------- + ----- +
 |  Pessoa |  Grupo |  Idade |
 + -------- + ------- + ----- +
 |  Bob |  1 |  32 |
 |  Jill |  1 |  34 |
 |  Shawn |  1 |  42 |
 |  Jake |  2 |  29 |
 |  Paul |  2 |  36 |
 |  Laura |  2 |  39 |
 + -------- + ------- + ----- +

Conjunto de resultados desejado:

 + -------- + ------- + ----- +
 |  Shawn |  1 |  42 |
 |  Jill |  1 |  34 |
 |  Laura |  2 |  39 |
 |  Paul |  2 |  36 |
 + -------- + ------- + ----- +

OBSERVAÇÃO: Esta questão baseia-se em um valor anterior – Obter registros com valor máximo para cada grupo de resultados de SQL agrupados – para obter uma única linha superior de cada grupo e que recebeu uma resposta específica do MySQL do @Bohemian:

select * from (select * from mytable order by `Group`, Age desc, Person) x group by `Group` 

Adoraria poder construir isso, embora eu não veja como.

Aqui está uma maneira de fazer isso, usando UNION ALL (consulte SQL Fiddle with Demo ). Isso funciona com dois grupos, se você tiver mais de dois grupos, precisará especificar o número do group e adicionar consultas para cada group :

 ( select * from mytable where `group` = 1 order by age desc LIMIT 2 ) UNION ALL ( select * from mytable where `group` = 2 order by age desc LIMIT 2 ) 

Há várias maneiras de fazer isso, consulte este artigo para determinar o melhor caminho para sua situação:

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

Editar:

Isso pode funcionar para você também, gera um número de linha para cada registro. Usando um exemplo do link acima, isso retornará apenas os registros com um número de linha menor ou igual a 2:

 select person, `group`, age from ( select person, `group`, age, (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number from test t CROSS JOIN (select @num:=0, @group:=null) c order by `Group`, Age desc, person ) as x where x.row_number < = 2; 

Ver demonstração

Em outros bancos de dados, você pode fazer isso usando ROW_NUMBER . O MySQL não suporta ROW_NUMBER mas você pode usar variables ​​para emular:

 SELECT person, groupname, age FROM ( SELECT person, groupname, age, @rn := IF(@prev = groupname, @rn + 1, 1) AS rn, @prev := groupname FROM mytable JOIN (SELECT @prev := NULL, @rn := 0) AS vars ORDER BY groupname, age DESC, person ) AS T1 WHERE rn < = 2 

Veja trabalhando on-line: sqlfiddle


Editar Acabei de notar que bluefeet postou uma resposta muito semelhante: +1 para ele. No entanto, esta resposta tem duas pequenas vantagens:

  1. É uma consulta única. As variables ​​são inicializadas dentro da instrução SELECT.
  2. Ele lida com laços conforme descrito na pergunta (ordem alfabética por nome).

Então vou deixar aqui caso possa ajudar alguém.

Tente isto:

 SELECT a.person, a.group, a.age FROM person AS a WHERE (SELECT COUNT(*) FROM person AS b WHERE b.group = a.group AND b.age >= a.age) < = 2 ORDER BY a.group ASC, a.age DESC 

DEMO

Que tal usar a auto-adesão:

 CREATE TABLE mytable (person, groupname, age); INSERT INTO mytable VALUES('Bob',1,32); INSERT INTO mytable VALUES('Jill',1,34); INSERT INTO mytable VALUES('Shawn',1,42); INSERT INTO mytable VALUES('Jake',2,29); INSERT INTO mytable VALUES('Paul',2,36); INSERT INTO mytable VALUES('Laura',2,39); SELECT a.* FROM mytable AS a LEFT JOIN mytable AS a2 ON a.groupname = a2.groupname AND a.age < = a2.age GROUP BY a.person HAVING COUNT(*) <= 2 ORDER BY a.groupname, a.age DESC; 

me dá:

 a.person a.groupname a.age ---------- ----------- ---------- Shawn 1 42 Jill 1 34 Laura 2 39 Paul 2 36 

Eu fui fortemente inspirado pela resposta de Bill Karwin ao Select top 10 records para cada categoria

Além disso, estou usando o SQLite, mas isso deve funcionar no MySQL.

Outra coisa: no acima, substituí a coluna group por uma coluna groupname por conveniência.

Editar :

Continuando com o comentário do OP sobre os resultados de empate em falta, aumentei a resposta de snuffin para mostrar todos os empates. Isso significa que se os últimos forem empatados, mais de 2 linhas podem ser retornadas, conforme mostrado abaixo:

 .headers on .mode column CREATE TABLE foo (person, groupname, age); INSERT INTO foo VALUES('Paul',2,36); INSERT INTO foo VALUES('Laura',2,39); INSERT INTO foo VALUES('Joe',2,36); INSERT INTO foo VALUES('Bob',1,32); INSERT INTO foo VALUES('Jill',1,34); INSERT INTO foo VALUES('Shawn',1,42); INSERT INTO foo VALUES('Jake',2,29); INSERT INTO foo VALUES('James',2,15); INSERT INTO foo VALUES('Fred',1,12); INSERT INTO foo VALUES('Chuck',3,112); SELECT a.person, a.groupname, a.age FROM foo AS a WHERE a.age >= (SELECT MIN(b.age) FROM foo AS b WHERE (SELECT COUNT(*) FROM foo AS c WHERE c.groupname = b.groupname AND c.age >= b.age) < = 2 GROUP BY b.groupname) ORDER BY a.groupname ASC, a.age DESC; 

me dá:

 person groupname age ---------- ---------- ---------- Shawn 1 42 Jill 1 34 Laura 2 39 Paul 2 36 Joe 2 36 Chuck 3 112 

Veja isso:

 SELECT p.Person, p.`Group`, p.Age FROM people p INNER JOIN ( SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group` UNION SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group` ) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group` ORDER BY `Group`, Age DESC, Person; 

SQL Fiddle: http://sqlfiddle.com/#!2/cdbb6/15

Se as outras respostas não forem rápidas o suficiente Experimente este código :

 SELECT province, n, city, population FROM ( SELECT @prev := '', @n := 0 ) init JOIN ( SELECT @n := if(province != @prev, 1, @n + 1) AS n, @prev := province, province, city, population FROM Canada ORDER BY province ASC, population DESC ) x WHERE n < = 3 ORDER BY province, n; 

Saída:

 +---------------------------+------+------------------+------------+ | province | n | city | population | +---------------------------+------+------------------+------------+ | Alberta | 1 | Calgary | 968475 | | Alberta | 2 | Edmonton | 822319 | | Alberta | 3 | Red Deer | 73595 | | British Columbia | 1 | Vancouver | 1837970 | | British Columbia | 2 | Victoria | 289625 | | British Columbia | 3 | Abbotsford | 151685 | | Manitoba | 1 | ... 

A solução de Snuffin parece muito lenta de executar quando você tem várias linhas e as soluções Mark Byers / Rick James e Bluefeet não funcionam no meu ambiente (MySQL 5.6) porque a ordem por é aplicada após a execução de select, então aqui está uma variante de soluções de Marc Byers / Rick James para corrigir este problema (com um select extra imbricado):

 select person, groupname, age from ( select person, groupname, age, (@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb, @prev:= groupname from ( select person, groupname, age from persons order by groupname , age desc, person ) as sortedlist JOIN (select @prev:=NULL, @rn :=0) as vars ) as groupedlist where rownumb< =2 order by groupname , age desc, person; 

Eu tentei consulta semelhante em uma tabela com 5 milhões de linhas e retorna o resultado em menos de 3 segundos

Eu queria compartilhar isso porque passei muito tempo procurando uma maneira fácil de implementar isso em um programa Java no qual estou trabalhando. Isso não dá a saída que você está procurando, mas está próximo. A function no mysql chamada GROUP_CONCAT() funcionou muito bem para especificar quantos resultados retornar em cada grupo. Usar LIMIT ou qualquer outra forma sofisticada de tentar fazer isso com COUNT não funcionou para mim. Então, se você está disposto a aceitar uma saída modificada, é uma ótima solução. Digamos que eu tenha uma tabela chamada ‘aluno’ com IDs de alunos, seu gênero e gpa. Vamos dizer que eu quero top 5 gpas para cada sexo. Então eu posso escrever a consulta como esta

 SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), ',',5) AS subcategories FROM student GROUP BY sex; 

Note que o parâmetro ‘5’ informa quantas inputs para concatenar em cada linha

E a saída seria algo como

 +--------+----------------+ | Male | 4,4,4,4,3.9 | | Female | 4,4,3.9,3.9,3.8| +--------+----------------+ 

Você também pode alterar a variável ORDER BY e ordená-los de uma maneira diferente. Então, se eu tivesse a idade do aluno, eu poderia replace o ‘gpa desc’ por ‘age desc’ e isso funcionaria! Você também pode adicionar variables ​​ao grupo por instrução para obter mais colunas na saída. Então, isso é apenas uma maneira que eu achei que é bastante flexível e funciona bem se você está bem com apenas listando os resultados.

Há uma resposta muito legal para esse problema no MySQL – Como obter as melhores linhas N por grupo

Com base na solução do link referenciado, sua consulta seria como:

 SELECT Person, Group, Age FROM (SELECT Person, Group, Age, @group_rank := IF(@group = Group, @group_rank + 1, 1) AS group_rank, @current_group := Group FROM `your_table` ORDER BY Group, Age DESC ) ranked WHERE group_rank < = `n` ORDER BY Group, Age DESC; 

onde n é o top n e your_table é o nome da sua tabela.

Eu acho que a explicação na referência é muito clara. Para referência rápida vou copiar e colar aqui:

Atualmente, o MySQL não suporta a function ROW_NUMBER () que pode atribuir um número de seqüência dentro de um grupo, mas como uma solução alternativa, podemos usar variables ​​de session do MySQL.

Essas variables ​​não exigem declaração e podem ser usadas em uma consulta para fazer cálculos e armazenar resultados intermediários.

@current_country: = country Este código é executado para cada linha e armazena o valor da coluna country para a variável @current_country.

@country_rank: = IF (@current_country = country, @country_rank + 1, 1) Neste código, se @current_country for o mesmo, incrementamos a sorting, caso contrário, defina-a como 1. Para a primeira linha, @current_country é NULL, então a sorting é também definido como 1.

Para uma sorting correta, precisamos ter o país ORDER BY, a população DESC

No SQL Server row_numer() é uma function poderosa que pode obter resultados facilmente como abaixo

 select Person,[group],age from ( select * ,row_number() over(partition by [group] order by age desc) rn from mytable ) t where rn < = 2