SQL sélectionner seulement les lignes avec une valeur maximale sur une colonne

Question

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

id

réviser

contenu

1

1

...

2

1

...

1

2

...

1

3

...

Comment sélectionner une ligne par id 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 contrôles dans le while boucle pour détecter et écraser les anciens revs de la série de résultats. Mais est-ce la seule méthode pour obtenir ce résultat ? N'existe-t-il pas une SQL solution ?

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

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

Answer 1

A première vue...

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

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 également.

Il s'agit d'une question très courante en SQL : trouver l'ensemble des données pour la ligne avec une certaine valeur maximale dans une colonne par un certain identifiant de groupe. J'ai souvent entendu cette question au cours de ma carrière. En fait, c'était l'une des questions auxquelles j'ai répondu lors de l'entretien technique de mon emploi actuel.

En fait, c'est tellement courant que la communauté StackOverflow a créé un tag unique pour traiter ce genre de questions : le plus grand-n par-groupe .

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

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

Dans cette approche, vous trouvez d'abord le 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 une é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

Jointure à gauche avec self, modification des conditions de jointure et des filtres

Dans cette approche, vous avez laissé la table se joindre à elle-même. L'égalité va dans le group-identifier . Alors, 2 mouvements intelligents :

1. La deuxième condition de jonction est que la valeur du côté gauche soit inférieure à la valeur du côté droit.
2. Lorsque vous effectuez l'étape 1, la ou les lignes qui ont effectivement la valeur maximum auront NULL dans la partie droite (c'est un LEFT JOIN vous vous souvenez ?). Ensuite, nous filtrons le résultat joint, en montrant seulement les lignes où le côté droit est NULL .

Donc vous vous retrouvez 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 aboutissent exactement au 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 avec SQL ANSI et fonctionneront donc avec votre SGBDR préféré, quelle que soit sa "saveur".

Les deux approches sont également favorables à la performance, mais les résultats peuvent varier (SGBDR, structure de la base de données, index, etc.). Ainsi, lorsque vous choisissez une approche plutôt que l'autre, repère . Et assurez-vous de choisir celui qui a le plus de sens pour vous.

Answer 2

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

Vous pouvez le faire en utilisant IN Essayez ceci :

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.

Answer 3

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 un simple lookup...

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

Alors que pour les balayages de tables complètes, les temps de subquery/leftjoin/corrélés sont de 6/8/9, lorsqu'il s'agit de recherches directes ou de lots ( 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 n'ai pas pu faire la différence entre les solutions leftjoin et correlated en termes de vitesse.

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

Answer 4

Je ne peux pas me porter garant des performances, mais voici une astuce inspirée des limites de Microsoft Excel. Elle présente quelques bonnes caractéristiques

BONNES CHOSES

• Il devrait forcer le retour d'un seul "enregistrement maximum", même en cas d'égalité (parfois utile).
• Il n'est pas nécessaire de se joindre

APPROCHE

C'est un peu laid et nécessite que vous sachiez quelque chose sur la gamme des valeurs valides de l'attribut réviser colonne. Supposons que nous connaissions le réviser La colonne est un nombre compris entre 0,00 et 999, décimales comprises, mais il n'y aura jamais que deux chiffres à droite de la virgule (par exemple, 34,17 serait une valeur valide).

En gros, vous créez une colonne synthétique unique en concaténant/assemblant le champ de comparaison primaire avec les données que vous souhaitez. De cette façon, vous pouvez forcer la fonction d'agrégation MAX() de SQL à renvoyer toutes les données (car elles ont été regroupées dans une seule colonne). Vous devez ensuite décompresser les données.

Voici comment cela se présente 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 par forcer le réviser pour être un nombre de longueur de caractères connue, quelle que soit la valeur de la colonne réviser de sorte que, par exemple

• 3.2 devient 1003.201
• 57 devient 1057.001
• 923.88 devient 1923.881

Si vous vous y prenez bien, la comparaison de deux nombres au moyen d'une chaîne de caractères devrait donner le même "maximum" que la comparaison numérique des deux nombres et il est facile de revenir au nombre d'origine en utilisant la fonction substring (qui est disponible sous une forme ou une autre à peu près partout).

Answer 5

Quelque chose comme ça ?

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