mysql plusieurs - SQL sélectionner uniquement les lignes avec la valeur maximale sur une colonne




colonnes having (25)

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.

Alors que pour les analyses de table complètes, les sous-requêtes subquery / leftjoin / correlated sont liées les unes aux autres au 6/8/9, en ce qui concerne les recherches directes ou batch ( id in (1,2,3) ), la sous-requête est beaucoup plus lente que les autres ( En raison de réexécuter 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 ...

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 doit contenir deux lignes: [1, 3, ...] et [2, 1, ..] . J'utilise MySQL .

Actuellement, j'utilise des contrôles dans la boucle while pour détecter et écraser les anciens revs du jeu de résultats. Mais est-ce la seule méthode pour atteindre le résultat? N'y a-t-il pas une solution SQL ?

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

Mise à jour 2
J'ai remarqué après avoir ajouté le sqlfiddle ci-dessus, 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.


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 OVER 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 

Ce 2 SELECT fonctionne bien sur Oracle 10g.


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 la variable group_concat_max_len . 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 la limite group_concat_max_len plus rapidement.


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 y a plus de types de fonctions de classement disponibles pour traiter un problème d'égalité: RANK, DENSE_RANK, PERSENT_RANK .


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.


Voici une bonne façon de le faire

Utilisez 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)

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 vraiment un problème.


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)

À première vue ...

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

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 également besoin de la colonne de content .

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é a créé une seule balise juste pour traiter des questions comme celle-ci: le greatest-n-per-group .

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

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

Dans cette approche, vous trouvez d'abord l' 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 l'égalité à la fois 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 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 l' 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 faites l'étape 1, la ligne (s) qui a réellement la valeur max aura NULL dans le côté droit (c'est un LEFT JOIN , souvenez-vous?). 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, l' indice de référence . Et assurez-vous de choisir celui qui vous convient le mieux.


La plupart des autres réponses, sinon toutes, conviennent aux petits ensembles de données. Pour la mise à l'échelle, plus de précaution est nécessaire. Voir here .

Il aborde plusieurs façons plus rapides de faire des groupwise max et top-N par groupe.


Si quelqu'un cherche une version de Linq, cela semble marcher 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) );
}

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

C'est un peu moche et nécessite que vous sachiez quelque chose sur la plage 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 comprises, mais qu'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'empaquetage commence en forçant la colonne rev à être un nombre de longueur de caractère connu indépendamment de la valeur de rev de telle 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).


Si vous avez plusieurs champs dans l'instruction select et que vous voulez la dernière valeur pour tous ces champs grâce au code optimisé:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

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
)

J'ai utilisé le ci-dessous pour résoudre un problème de mon cru. J'ai d'abord créé une table temporaire et inséré la valeur max rev par identifiant 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 / contenu possibles. En faisant cela, je filtre naturellement les combinaisons id / contenu non-maximum, et je me retrouve avec les seules valeurs de rev maximales pour chacun.

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

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

PAS mySQL , mais pour d'autres personnes trouvant cette question et utilisant SQL, une autre façon de résoudre le problème le greatest-n-per-group 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 dans SqlFiddle


Voici une autre solution pour récupérer les enregistrements uniquement 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)

SELECT * FROM Employé où Employee.Salary dans (sélectionnez max (salary) du groupe Employee par Employe_id) ORDER BY Employee.Salary


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

Vous pouvez le faire en utilisant IN :

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.


voici une autre solution espère que cela va aider 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

Je voudrais utiliser 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

La sous-requête SELECT n'est peut-être pas trop efficace, mais la clause JOIN semble être utilisable. Je ne suis pas un expert dans l'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ésolution identique à votre 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 des tables ayant des milliers d'enregistrements, et il faut moins de 0,01 seconde sur une machine vraiment pas trop forte.

Je n'utiliserais pas la clause IN (comme mentionné plus haut). IN est donné pour être utilisé avec de courtes listes de constans, et non pour être le filtre de requête construit sur la sous-requête. C'est parce que la sous-requête dans IN est effectuée pour chaque enregistrement scanné, ce qui peut rendre la requête très loooong temps.


select * from yourtable
group by id
having rev=max(rev);

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

Dans SQL Server 2008, vous pouvez insérer plusieurs lignes à l'aide d'une seule instruction SQL INSERT.

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

Pour plus d'informations, consultez le cours MOC 2778A - Ecriture de requêtes SQL dans SQL Server 2008.





mysql sql aggregate-functions greatest-n-per-group