SQL sélectionnez uniquement les lignes avec la valeur de max 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 choisir 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 .

J'utilise actuellement des contrôles dans la boucle while pour détecter et sur-Écrire les vieux révs à partir du jeu de résultats. Mais est c'est la seule méthode pour atteindre le résultat? N'y a-t-il pas une solution SQL ?

mise à Jour

Comme les réponses le suggèrent, il est une solution SQL, et ici une démo sqlfiddle .

Update 2

J'ai remarqué après avoir ajouté le ci-dessus sqlfiddle , la vitesse à laquelle la question est soulevée a dépassé le taux de réponses positives. Qui n'a pas été l'intention! Le violon est basé sur les réponses, en particulier la réponse acceptée.

914
demandé sur DineshDB 2011-10-12 23:42:07
la source

29 ответов

à première vue...

Tous vous avez besoin est un GROUP BY la clause de l' 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 aussi besoin de la colonne content .

c'est une question très courante en SQL: trouver les données complètes pour la ligne avec une valeur max dans une colonne par un identifiant de groupe. J'ai entendu dire que beaucoup au cours de mon carrière. En fait, c'était l'une des questions auxquelles j'ai répondu dans l'entrevue technique de mon emploi actuel.

il est, en fait, si commun que StackOverflow community a créé une seule étiquette juste pour répondre à des questions comme celle-ci: .

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

Adhérer avec de simples group-identifier, max-value-in-group Sous-requête

dans ce approche, vous trouvez d'abord le group-identifier, max-value-in-group (déjà résolu ci-dessus) dans une sous-requête. Puis vous rejoignez votre table à la sous-requête avec égalité sur 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 de la Rejoindre avec soi, le fait de modifier les conditions de jointure et les filtres

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

  1. la deuxième jointure la condition est que la valeur du côté gauche est inférieure à la valeur du côté droit
  2. quand vous faites l'étape 1, la ou les lignes qui ont réellement la valeur max auront NULL dans le côté droit (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 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 vos RDBMS préférés, indépendamment de son"goût".

les deux approches sont également favorables à la performance, mais votre kilométrage peut varier (RDBMS, structure de PD, indices, etc.). Donc quand vous choisissez une approche plutôt que l'autre, de référence", 1519580920" . Et assurez-vous de choisir celle qui font le plus de sens pour vous.

1467
répondu Adrian Carneiro 2015-11-08 14:52:52
la source

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
)

pour moi, c'est moins compliqué... plus facile à lire et à maintenir.

182
répondu Kevin Burton 2013-12-16 17:08:50
la source

une autre solution consiste à utiliser une sous-quantité 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)

ayant 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 (subquery, leftjoin), basées sur des mesures MySQL avec un tableau InnoDB d'environ 1 million d'enregistrements, La Taille du groupe étant: 1-3.

tandis que pour les scans de table complète, les sous-ensembles/fuseaux horaires/temps corrélés se rapportent pour l'autre comme 6/8/9, quand il s'agit de recherches directes ou de lot ( id in (1,2,3) ), subquery est beaucoup plus lent que les autres (en raison de la rediffusion de la subquery). Cependant, je ne pouvais pas faire la différence entre les solutions de leftjoin et corrélées dans la vitesse.

une dernière remarque, comme leftjoin crée n*(n+1)/2 joint dans les groupes, sa performance peut être fortement affectée par la taille des groupes...

57
répondu Vajk Hermecz 2014-01-23 18:16:11
la source

Je ne peux pas répondre de la performance, mais voici un truc inspiré par les limites de Microsoft Excel. Il a quelques bonnes caractéristiques

BONNES CHOSES

  • Elle doit forcer le retour d'un seul "max record", même s'il y a égalité (parfois utile)
  • On n'a pas besoin de jointure

"approche

c'est un petit peu laid et exige que vous sachiez quelque chose sur la gamme de valeurs valides de la colonne rev . Supposons que nous savons que la colonne rev est un nombre compris entre 0.00 et 999 décimales, mais qu'il n'y aura jamais que deux chiffres à droite du point décimal (par exemple 34.17 serait une valeur valide).

l'essentiel de la chose est que vous créez un seul colonne synthétique par chaîne concaténant/empaquetant le champ de comparaison primaire avec les données que vous voulez. De cette façon, vous pouvez forcer la fonction d'agrégat max() de SQL à retourner toutes les données (parce qu'elles ont été empaquetées dans une seule colonne). Ensuite, vous devez déballer les données.

voici à quoi il 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 par forcer le rev colonne pour être un nombre de longueur de caractère connue indépendamment de la valeur de rev 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 chaîne de deux nombres devrait donner le même "max" que la comparaison numérique des deux nombres et il est facile à convertir de nouveau au nombre original en utilisant la fonction de substrat (qui est disponible sous une forme ou une autre à peu près partout).

39
répondu David Foster 2013-06-30 10:02:30
la source

je suis sidéré qu'aucune réponse n'a offert fenêtre SQL de la fonction solution:

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 la norme SQL ANSI / norme ISO SQL: 2003 et plus tard étendu avec ANSI / norme ISO SQL: 2008, les fonctions de fenêtre (ou fenêtrage) sont disponibles avec tous les principaux fournisseurs maintenant. Il y a plus de types de fonctions de grade disponibles pour traiter une question d'égalité: RANK, DENSE_RANK, PERSENT_RANK .

36
répondu topchef 2016-08-15 02:16:43
la source

je pense que c'est la solution la plus facile:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • sélectionnez * : Retournez tous les champs.
  • DE l'Employé : Table de recherche.
  • (SELECT *...) subquery : retourner toutes les personnes, triées par Salaire.
  • groupe par employesub.Salaire:: forcez la rangée de salaire triée en haut de chaque employé à être le résultat retourné.

si vous avez besoin d'un seul row, c'est encore plus facile:

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:

  • ordre de L'employé.Salaire DESC: ordonnez les résultats par salaire, avec les salaires les plus élevés en premier.
  • limite 1: retourner un seul résultat.

comprendre cette approche, résoudre l'un de ces problèmes similaires devient trivial: obtenez l'employé avec le plus bas salaire (changer de DESC à ASC), obtenez les dix employés les mieux rémunérés (changer la limite 1 pour la limite 10), triez au moyen d'une autre zone (changer l'ordre par employé.Salaire à commander par employé.Commission), etc..

23
répondu HoldOffHunger 2017-11-27 19:07:29
la source

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)
15
répondu Marc B 2011-10-12 23:54:33
la source

puisque c'est la question la plus populaire en ce qui concerne ce problème, je vais re-post une autre réponse à elle ici aussi bien:

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

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

s'il vous Plaît de crédit réponse de l'utilisateur de Bohème dans cette question de fournir un tel concis et élégant la réponse à 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 GROUP BY statement retournera des valeurs significatives pour les colonnes qui ne sont pas dans GROUP BY list. Alors utilisez cette solution à vos propres risques

5
répondu Yura 2017-05-23 15:34:54
la source

j'aime utiliser une solution basée sur NOT EXIST pour ce problème:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
5
répondu Bulat 2017-07-17 04:46:12
la source

une troisième solution que je vois à peine mentionnée est MySQL spécifique 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 ça a l'air horrible (conversion en chaîne et retour etc.), mais dans mon expérience, il est généralement plus rapide que les autres solutions. Peut-être que pour mon cas d'utilisation, mais je l'ai utilisé sur des tables avec des millions d'enregistrements et de nombreux identifiants uniques. Peut-être que C'est parce que MySQL est assez mauvais à l'optimisation des autres solutions (au moins dans les 5.0 jours où je suis venu avec ce solution.)

une chose importante est que GROUP_CONCAT a une longueur maximale pour la chaîne qu'il peut construire. Vous voulez probablement augmenter cette limite en définissant la variable group_concat_max_len . Et gardez à l'esprit que ce sera une limite de 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 voulez probablement utiliser un séparateur différent, comme \0 peut-être. Vous aurez également exécuter dans la limite group_concat_max_len plus rapide.

4
répondu Jannes 2014-10-10 15:57:00
la source

si vous avez beaucoup de champs dans la déclaration select et vous voulez la dernière valeur pour tous ces champs par le code optimisé:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 
3
répondu seahawk 2015-09-04 08:33:22
la source

Que pensez-vous de ceci:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id
2
répondu inor 2014-05-30 17:38:12
la source

j'utiliserais ceci:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

subquery SELECT n'est peut-être pas trop efficace, mais dans la clause JOIN semble être utilisable. Je ne suis pas un expert en optimisation des requêtes, mais J'ai essayé MySQL, PostgreSQL, FireBird et ça marche très bien.

vous pouvez utiliser ce schéma dans plusieurs jointures et avec la clause WHERE. C'est mon exemple de travail (résoudre identique au vôtre problème avec la table "firmy"):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

il est demandé sur les tables ayant des ados thusands de disques, et il prend moins de 0,01 seconde sur machine vraiment pas trop forte.

Je ne voudrais pas utiliser dans la clause (comme il est mentionné quelque part ci-dessus). IN est donné à utiliser avec de courtes listes de constans, et non pas comme étant le filtre de requête construit sur subquery. C'est parce que subquery in IN IN est effectuée pour chaque enregistrement scanné qui peut faire requête prenant très loooong temps.

2
répondu Marek Wysmułek 2015-03-04 21:12:10
la source

pas mySQL , mais pour d'autres personnes trouvant cette question et en utilisant SQL, une autre façon de résoudre le problème est d'utiliser Cross Apply dans MS SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

voici un exemple en SqlFiddle

2
répondu KyleMit 2018-08-17 17:55:51
la source

cette solution ne fait qu'une sélection de votre table, elle est donc plus rapide. Il ne fonctionne que pour MySQL et SQLite (pour SQLite supprimer DESC) selon le test sur sqlfiddle.com. Peut-être peut-il être modifié pour travailler sur d'autres langues que je ne connais pas.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id
1
répondu plavozont 2014-03-17 12:28:23
la source

Voici une bonne façon de faire que

utiliser le code suivant:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
1
répondu shay 2015-01-07 15:11:59
la source

j'aime le faire en classant les records par colonne. Dans ce cas, classer rev valeurs regroupées par id . Ceux qui ont un rev plus élevé auront des classements plus bas. Donc le plus haut rev aura le classement de 1.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Je ne suis pas sûr que l'introduction de variables ralentisse tout. Mais au moins je ne demande pas YOURTABLE deux fois.

1
répondu user5124980 2015-07-16 21:52:31
la source

Si quelqu'un est à la recherche d'un Linq verson, cela semble fonctionner pour moi:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}
1
répondu Dirk Boer 2015-09-17 16:22:19
la source

a trié le champ rev dans l'ordre inverse puis groupé par id qui a donné la première ligne de chaque groupe qui est celui avec la plus haute valeur de rev.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

testé dans http://sqlfiddle.com / avec les données suivantes

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

cela a donné le résultat suivant dans MySql 5.5 et 5.6

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two
1
répondu blokeish 2015-12-11 06:14:18
la source

voici une autre solution j'espère qu'elle aidera quelqu'un

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
1
répondu Abdul Samad 2017-06-20 13:10:35
la source

aucune de ces réponses n'a fonctionné pour moi.

C'est ce qui a fonctionné pour moi.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
1
répondu qaisjp 2017-07-13 21:19:20
la source

sélectionner * De L'employé quand l'Employé.Salaire en (sélectionnez max (salaire) du groupe D'employés par Employe_id) COMMANDE PAR Employé.Salaire 151910920"

1
répondu guru008 2017-07-30 21:12:46
la source

Voici une autre solution pour récupérer les enregistrements seulement avec un champ qui a la valeur maximale pour ce champ. Cela fonctionne pour SQL400 qui est la plate-forme sur laquelle je travaille. Dans cet exemple, les enregistrements avec la valeur maximale dans le champ FIELD5 seront récupérés par L'instruction SQL suivante.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)
1
répondu Cesar 2017-10-17 03:18:19
la source

j'ai utilisé le dessous pour résoudre un problème de mon propre. J'ai d'abord créé une table temp et inséré la valeur max rev par id unique.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

j'ai ensuite joint ces valeurs max (#temp1) à toutes les combinaisons id/content possibles. En faisant cela, je filtre naturellement les combinaisons id/content non-maximum, et je me retrouve avec les seules valeurs de Rév max pour chacune.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id
0
répondu Richard Ball 2018-01-05 13:51:51
la source

une autre façon de faire le travail est D'utiliser la fonction analytique MAX() dans la clause OVER PARTITION

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

l'autre solution de sur PARTITION déjà documentée dans ce post est

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

Cette 2 SÉLECTIONNEZ le travail bien sur Oracle 10g.

0
répondu schlebe 2018-02-20 12:07:22
la source

vous pouvez faire le select sans jointure quand vous combinez le rev et id dans un maxRevId valeur pour MAX() et puis le diviser à nouveau aux valeurs originales:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

c'est particulièrement rapide quand il ya une jointure complexe au lieu d'une seule table. Avec les approches traditionnelles, la jonction complexe se ferait deux fois.

la combinaison ci-dessus est simple avec des fonctions de bits lorsque rev et id sont INT UNSIGNED (32 bits) et la valeur combinée correspond à BIGINT UNSIGNED (64 bits). Lorsque les id et rev sont plus grands que les valeurs 32 bits ou sont constitués de plusieurs colonnes, vous devez combiner la valeur en par exemple une valeur binaire avec un rembourrage approprié pour MAX() .

0
répondu zovio 2018-09-17 12:08:22
la source
select * from yourtable
group by id
having rev=max(rev);
-1
répondu Terry 2013-07-11 17:12:55
la source

cela fonctionne pour moi dans sqlite3:

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

avec *, vous obtenez une colonne rev dupliquée, mais ce n'est pas un gros problème.

-2
répondu Fredrik Eldh 2015-01-08 19:49:05
la source
SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;
-4
répondu Tricky 2013-06-18 12:06:23
la source