MySQL: Selecione N linhas, mas com apenas valores exclusivos em uma coluna

Question

Oct 10, 2008, 12:11 PM

unique aggregate-functions sql min mysql

MySQL: Selecione N linhas, mas com apenas valores exclusivos em uma coluna

Dado este conjunto de dados:

ID  Name            City            Birthyear
1   Egon Spengler   New York        1957
2   Mac Taylor      New York        1955
3   Sarah Connor    Los Angeles     1959
4   Jean-Luc Picard La Barre        2305
5   Ellen Ripley    Nostromo        2092
6   James T. Kirk   Riverside       2233
7   Henry Jones     Chicago         1899

Eu preciso encontrar as 3 pessoas mais velhas, mas apenas uma de cada cidade.

Se fosse apenas os três mais antigos, seria ...

Henry Jones / ChicagoMac Taylor / Nova IorqueEgon Spengler / Nova Iorque

No entanto, como Egon Spengler e Mac Taylor estão localizados em Nova York, Egon Spengler desistiu e o próximo (Sarah Connor / Los Angeles) viria.

Alguma solução elegante?

Atualizar:

Atualmente uma variação do PConroy é a melhor solução / mais rápida:

SELECT P.*, COUNT(*) AS ct
   FROM people P
   JOIN (SELECT MIN(Birthyear) AS Birthyear
              FROM people 
              GROUP by City) P2 ON P2.Birthyear = P.Birthyear
   GROUP BY P.City
   ORDER BY P.Birthyear ASC 
   LIMIT 10;

Sua consulta original com "IN" é extremamente lenta com grandes conjuntos de dados (abortados após 5 minutos), mas mover a subconsulta para um JOIN acelerará bastante. Demorou cerca de 0,15 segundos por aprox. 1 milhão de linhas no meu ambiente de teste. Eu tenho um índice em "Cidade, Birthyear" e um segundo apenas em "Birthyear".

Nota: Isto está relacionado com ...

Selecionando linhas exclusivas em um conjunto de duas possibilidades Consulta SQL para obter o preço mais recente