Question SQL sélectionner uniquement les lignes avec la valeur maximale sur une colonne


J'ai ce tableau pour les documents (version simplifiée ici):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Comment puis-je sélectionner une ligne par identifiant et seulement le plus grand rev?
Avec les données ci-dessus, le résultat devrait contenir deux lignes: [1, 3, ...] et [2, 1, ..]. j'utilise MySQL.

Actuellement, j'utilise des chèques dans while boucle pour détecter et écraser les vieux révolutions du jeu de résultats. Mais est-ce la seule méthode pour atteindre le résultat? N'y at-il pas un SQL Solution?

Mettre à jour
Comme les réponses le suggèrent, il y a est une solution SQL, et voici une démo sqlfiddle.

Mise à jour 2
J'ai remarqué après avoir ajouté ce qui précède sqlfiddle, le taux auquel la question est upvoted a dépassé le taux upvote des réponses. Cela n'a pas été l'intention! Le violon est basé sur les réponses, en particulier la réponse acceptée.


870
2017-10-12 19:42


origine


Réponses:


À première vue ...

Tout ce dont vous avez besoin est un GROUP BY clause avec le MAX fonction d'agrégat:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Ce n'est jamais aussi simple, n'est-ce pas?

Je viens de remarquer que vous avez besoin du content colonne aussi bien.

C'est une question très courante dans SQL: trouvez toutes les données de la ligne avec une valeur maximale dans une colonne pour un identificateur de groupe. J'en ai beaucoup entendu parler pendant ma carrière. En fait, c'était l'une des questions auxquelles j'ai répondu dans l'interview technique de mon travail actuel.

Il est, en fait, si commun que la communauté StackOverflow a créé un seul tag juste pour traiter des questions comme ça: .

Fondamentalement, vous avez deux approches pour résoudre ce problème:

Rejoindre avec simple group-identifier, max-value-in-group Sous-requête

Dans cette approche, vous trouvez d'abord group-identifier, max-value-in-group (déjà résolu ci-dessus) dans une sous-requête. Ensuite, vous joignez votre table à la sous-requête avec égalité sur les deux group-identifier et max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Gauche Se joindre à soi-même, modifier les conditions de jointure et les filtres

Dans cette approche, vous avez quitté rejoindre la table avec lui-même. L'égalité, bien sûr, va dans le group-identifier. Ensuite, 2 mouvements intelligents:

  1. La deuxième condition de jointure est d'avoir la valeur du côté gauche inférieure à la bonne valeur
  2. Lorsque vous effectuez l'étape 1, la ou les lignes qui ont réellement la valeur max auront NULL dans le bon côté (c'est un LEFT JOIN, rappelles toi?). Ensuite, nous filtrons le résultat joint, en montrant seulement les lignes où le côté droit est NULL.

Donc vous finissez avec:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Les deux approches apportent exactement le même résultat.

Si vous avez deux lignes avec max-value-in-group pour group-identifier, les deux lignes seront dans le résultat dans les deux approches.

Les deux approches sont compatibles SQL ANSI, donc, fonctionnera avec votre SGBDR favori, indépendamment de sa "saveur".

Les deux approches sont également compatibles avec les performances, mais votre kilométrage peut varier (RDBMS, structure DB, index, etc.). Donc, quand vous choisissez une approche par rapport à l'autre, référence. Et assurez-vous de choisir celui qui vous convient le mieux.


1387
2017-10-12 19:43



Ma préférence est d'utiliser le moins de code possible ...

Vous pouvez le faire en utilisant IN essaye ça:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

à mon avis, c'est moins compliqué ... plus facile à lire et à maintenir.


168
2017-10-12 19:47



Une autre solution consiste à utiliser une sous-requête corrélée:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Avoir un index sur (id, rev) rend la sous-requête presque comme une simple recherche ...

Voici des comparaisons avec les solutions de la réponse de @ AdrianCarneiro (sous-requête, leftjoin), basées sur des mesures MySQL avec une table InnoDB d'environ 1 million d'enregistrements, la taille du groupe étant: 1-3.

Tandis que pour les analyses de table complètes, les sous-requêtes subquery / leftjoin / correlated se rapportent les unes aux autres au 6/8/9, lorsqu'il s'agit de recherches directes ou par lot (id in (1,2,3)), la sous-requête est beaucoup plus lente que les autres (en raison de la réexécution de la sous-requête). Cependant je ne pourrais pas différencier entre leftjoin et solutions corrélées dans la vitesse.

Une dernière note, comme leftjoin crée n * (n + 1) / 2 jointures dans les groupes, ses performances peuvent être fortement affectées par la taille des groupes ...


52
2018-01-23 14:16



Je ne peux pas garantir la performance, mais voici une astuce inspirée par les limites de Microsoft Excel. Il a quelques bonnes caractéristiques

BON PRODUIT

  • Il devrait forcer le retour d'un seul "enregistrement max" même s'il y a un lien (parfois utile)
  • Il ne nécessite pas de jointure

APPROCHE

Il est un peu moche et exige que vous sachiez quelque chose sur la gamme de valeurs valides de la tour colonne. Supposons que nous connaissons le tour colonne est un nombre compris entre 0,00 et 999, y compris les décimales, mais il n'y aura jamais que deux chiffres à droite de la virgule décimale (par exemple, 34,17 serait une valeur valide).

L'essentiel de la chose est que vous créez une seule colonne synthétique par chaîne de concaténation / emballage du champ de comparaison primaire avec les données que vous voulez. De cette manière, vous pouvez forcer la fonction d'agrégat MAX () SQL à retourner toutes les données (car elles ont été regroupées dans une seule colonne). Ensuite, vous devez déballer les données.

Voici à quoi cela ressemble avec l'exemple ci-dessus, écrit en SQL

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

L'emballage commence en forçant le tour colonne pour être un nombre de longueur de caractères connus indépendamment de la valeur de tour de sorte que par exemple

  • 3.2 devient 1003.201
  • 57 devient 1057.001
  • 923,88 devient 1923,881

Si vous le faites correctement, la comparaison de deux nombres devrait donner le même résultat que la comparaison numérique des deux nombres et il est facile de revenir au nombre original en utilisant la fonction de sous-chaîne (qui est disponible sous une forme ou une autre partout).


34
2018-06-30 06:02



Je suis sidéré que pas de réponse offerte solution de fonction de fenêtre SQL:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Ajouté dans SQL standard ANSI / ISO Standard SQL: 2003 et plus tard étendu avec ANSI / ISO Standard SQL: 2008, les fonctions de fenêtre (ou de fenêtrage) sont maintenant disponibles avec tous les principaux fournisseurs. Il existe plusieurs types de fonctions de classement disponibles pour traiter un problème d'égalité: RANK, DENSE_RANK, PERSENT_RANK.


27
2017-08-09 15:29



Je pense que c'est la solution la plus simple:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: retourne tous les champs.
  • De l'employé: Table recherché sur.
  • Sous-requête (SELECT * ...): renvoie toutes les personnes, triées par salaire.
  • GROUP BY employeesub.Salary:: Force la ligne de salaire triée par le haut de chaque employé à être le résultat renvoyé.

Si vous n'avez besoin que d'une seule ligne, c'est encore plus simple:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Je pense aussi que c'est le plus facile à décomposer, comprendre et modifier à d'autres fins:

  • ORDER BY Employee.Salary DESC: Ordonner les résultats par le salaire, avec les salaires les plus élevés en premier.
  • LIMITE 1: Renvoie un seul résultat.

Comprendre cette approche, résoudre l'un de ces problèmes similaires devient trivial: obtenir l'employé avec le salaire le plus bas (changer DESC à ASC), obtenir top-10 salariés (changer LIMIT 1 à LIMIT 10), trier au moyen d'un autre champ (changer ORDER BY Employee.Salary à ORDER BY Employee.Commission), etc.


20
2017-09-14 00:28



Quelque chose comme ça?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

14
2017-10-12 19:48



Puisque c'est la question la plus populaire en ce qui concerne ce problème, je vais re-poster une autre réponse ici aussi:

Il semble qu'il y ait une façon plus simple de le faire (mais seulement en MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Veuillez créditer la réponse de l'utilisateur Bohemian dans cette question pour fournir une réponse aussi concise et élégante à ce problème.

EDIT: bien que cette solution fonctionne pour beaucoup de gens, elle peut ne pas être stable à long terme, puisque MySQL ne garantit pas que l'instruction GROUP BY retournera des valeurs significatives pour les colonnes qui ne sont pas dans la liste GROUP BY. Alors utilisez cette solution à vos risques et périls


6
2017-07-03 14:33



Une troisième solution que je vois rarement mentionné est spécifique à MySQL et ressemble à ceci:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Oui, il a l'air horrible (conversion en chaîne et en arrière, etc.) mais dans mon expérience, il est généralement plus rapide que les autres solutions. Peut-être que juste pour mes cas d'utilisation, mais je l'ai utilisé sur des tables avec des millions d'enregistrements et de nombreux ID uniques. C'est peut-être parce que MySQL est plutôt mauvais pour optimiser les autres solutions (au moins dans les 5.0 jours quand j'ai eu cette solution).

Une chose importante est que GROUP_CONCAT a une longueur maximale pour la chaîne qu'il peut construire. Vous souhaitez probablement augmenter cette limite en définissant le group_concat_max_len variable. Et gardez à l'esprit que ce sera une limite sur la mise à l'échelle si vous avez un grand nombre de lignes.

Quoi qu'il en soit, ce qui précède ne fonctionne pas directement si votre champ de contenu est déjà du texte. Dans ce cas, vous voudrez probablement utiliser un séparateur différent, comme \ 0 peut-être. Vous rencontrerez également le group_concat_max_len limiter plus vite.


4
2017-10-10 11:57



J'aime utiliser un NOT EXISTsolution basée sur ce problème:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

4
2017-09-05 21:58