MySQL : Sélectionner N lignes, mais avec seulement des valeurs uniques dans une colonne

Question

Étant donné cet ensemble de données :

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

Je dois trouver les 3 personnes les plus âgées, mais seulement une de chaque ville.

Si c'était juste les trois plus vieux, ce serait...

• Henry Jones / Chicago
• Mac Taylor / New York
• Egon Spengler / New York

Cependant, comme Egon Spengler et Mac Taylor sont tous deux situés à New York, Egon Spengler serait éliminé et le suivant (Sarah Connor / Los Angeles) entrerait en scène à sa place.

Des solutions élégantes ?

Mise à jour :

Actuellement, une variation de PConroy est la solution la meilleure/la plus rapide :

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;

Sa requête originale avec "IN" est extrêmement lente avec de grands ensembles de données (elle est abandonnée après 5 minutes), mais le fait de déplacer la sous-requête vers un JOIN l'accélère considérablement. Il a fallu environ 0,15 seconde pour environ 1 million de lignes dans mon environnement de test. J'ai un index sur "Ville, année de naissance" et un deuxième index sur "Année de naissance".

Note : Ceci est lié à...

• Sélection de lignes uniques dans un ensemble de deux possibilités
• Requête SQL pour obtenir le dernier prix

Answer 1

Ce n'est sans doute pas la solution la plus élégante, et les performances de la IN peut souffrir sur des tables plus grandes.

La requête imbriquée obtient le minimum Birthyear pour chaque ville. Seuls les enregistrements qui ont ce Birthyear sont trouvés dans la requête externe. En classant par âge puis en limitant à 3 résultats, on obtient les 3 personnes les plus âgées qui sont aussi les plus âgées de leur ville (Egon Spengler n'est pas retenu ).

SELECT Name, City, Birthyear, COUNT(*) AS ct
FROM table
WHERE Birthyear IN (SELECT MIN(Birthyear)
               FROM table
               GROUP by City)
GROUP BY City
ORDER BY Birthyear DESC LIMIT 3;

+-----------------+-------------+------+----+
| name            | city        | year | ct |
+-----------------+-------------+------+----+
| Henry Jones     | Chicago     | 1899 | 1  |
| Mac Taylor      | New York    | 1955 | 1  |
| Sarah Connor    | Los Angeles | 1959 | 1  |
+-----------------+-------------+------+----+

Modifier - ajouté GROUP BY City à la requête externe, car les personnes ayant la même année de naissance renverraient plusieurs valeurs. Le regroupement sur la requête externe garantit qu'un seul résultat sera renvoyé par ville, si plus d'une personne possède ce minimum. Birthyear . Le site ct montrera si plus d'une personne existe dans la ville avec ce nom. Birthyear

Answer 2

Ce n'est probablement pas la solution la plus élégante et la plus rapide, mais elle devrait fonctionner. J'ai hâte de voir les solutions des vrais gourous des bases de données.

select p.* from people p,
(select city, max(age) as mage from people group by city) t
where p.city = t.city and p.age = t.mage
order by p.age desc

Answer 3

Quelque chose comme ça ?

SELECT
  Id, Name, City, Birthyear
FROM
  TheTable
WHERE
  Id IN (SELECT TOP 1 Id FROM TheTable i WHERE i.City = TheTable.City ORDER BY Birthyear)

Answer 4

Ce n'est pas très joli, mais cela devrait aussi fonctionner avec plusieurs personnes ayant le même nom :

Données d'essai :

select id, name, city, dob 
into people
from
(select 1 id,'Egon Spengler' name, 'New York' city , 1957 dob
union all select 2, 'Mac Taylor','New York', 1955
union all select 3, 'Sarah Connor','Los Angeles', 1959
union all select 4, 'Jean-Luc Picard','La Barre', 2305
union all select 5, 'Ellen Ripley','Nostromo', 2092
union all select 6, 'James T. Kirk','Riverside', 2233
union all select 7, 'Henry Jones','Chicago', 1899
union all select 8, 'Blah','New York', 1955) a

Une requête :

select 
    * 
from 
    people p
    left join people p1
    ON 
        p.city = p1.city
        and (p.dob > p1.dob and p.id <> p1.id)
        or (p.dob = p1.dob and p.id > p1.id)
where
    p1.id is null
order by 
    p.dob

Answer 5

@BlaM

MISE À JOUR Je viens de découvrir qu'il est bon d'utiliser USING au lieu de ON. Cela supprimera les colonnes en double dans le résultat.

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

POSTE ORIGINAL

Bonjour, j'ai essayé d'utiliser votre requête mise à jour mais j'obtenais des résultats erronés jusqu'à ce que j'ajoute une condition supplémentaire à la jointure (également une colonne supplémentaire dans la sélection de la jointure). transféré à votre requête, j'utilise ceci :

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

en théorie, vous ne devriez pas avoir besoin du dernier GROUP BY P.City, mais je l'ai laissé là pour l'instant, juste au cas où. je le supprimerai probablement plus tard.