SQL sélectionnez uniquement les lignes avec la valeur de max sur une colonne

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.

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

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

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 .

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

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)

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

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
)

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.

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 

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

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.

pas mySQL , mais pour d'autres personnes trouvant cette question et en utilisant SQL, une autre façon de résoudre le le plus-n-Par-Groupe 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

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

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)

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.

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) );
}

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

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

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

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"

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)

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

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.

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() .

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

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.

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;