MySQL - Contrôlez quelle ligne est retournée par un group by

Question

J'ai une table de base de données comme celle-ci :

id    version_id    field1    field2
1     1             texta      text1
1     2             textb      text2
2     1             textc      text3
2     2             textd      text4
2     3             texte      text5

Si vous ne l'avez pas compris, il contient un certain nombre de versions d'une ligne, puis des données textuelles.

Je veux l'interroger et retourner la version avec le numéro le plus élevé pour chaque id. (donc les deuxième et dernière lignes seulement dans l'exemple ci-dessus).

J'ai essayé d'utiliser group by tout en ordonnant par version_id DESC - mais il semble que l'ordre soit donné après le regroupement, donc cela ne fonctionne pas.

Quelqu'un a une idée ? Je n'arrive pas à croire que c'est impossible !

UPDATE :

J'ai trouvé ça, qui fonctionne, mais qui utilise une sous-requête :

SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id

Answer 1

Cela s'appelle sélectionner le maximum groupé d'une colonne. Voici plusieurs approches différentes pour mysql.

Voici comment je m'y prendrais :

SELECT *
FROM (SELECT id, max(version_id) as version_id FROM table GROUP BY id) t1
INNER JOIN table t2 on t2.id=t1.id and t1.version_id=t2.version_id

Cela sera relativement efficace, même si mysql créera une table temporaire en mémoire pour la sous-requête. Je suppose que vous avez déjà un index sur (id, version_id) pour cette table.

C'est un défaut de SQL que vous devez plus ou moins utiliser une sous-requête pour ce type de problème ( semi-joint sont un autre exemple).

Les sous-requêtes ne sont pas bien optimisées dans mysql mais les sous-requêtes non corrélées ne sont pas si mauvaises tant qu'elles ne sont pas si énormes qu'elles seront écrites sur le disque plutôt qu'en mémoire. Étant donné que cette requête ne comporte que deux ints, la sous-requête pourrait contenir des millions de lignes bien avant que cela n'arrive, mais la sous-requête select * de votre première requête pourrait souffrir de ce problème bien plus tôt.

Answer 2

Je pense que ça pourrait le faire, mais je ne suis pas sûr que ce soit le meilleur ou le plus rapide.

SELECT * FROM table 
WHERE (id, version_id) IN 
  (SELECT id, MAX(version_id) FROM table GROUP BY id)

Answer 3

SELECT id, version_id, field1, field2
FROM (
    SELECT @prev = id AS st, (@prev := id), m.*
    FROM (
           (SELECT @prev := NULL) p,
           (
            SELECT *
            FROM   mytable
            ORDER BY
                   id DESC, version_id DESC
           ) m
     ) m2
WHERE NOT IFNULL(st, FALSE);

Pas de sous-requêtes, une seule passe sur UNIQUE INDEX ON MYTABLE (id, version_id) si vous en avez un (ce que je pense que vous devriez faire)

Answer 4

Cette requête fera l'affaire sans groupe de référence :

SELECT * FROM table AS t
LEFT JOIN table AS t2 
    ON t.id=t2.id 
    AND t.version_id < t2.version_id
WHERE t2.id IS NULL

Il n'a pas besoin de tables temporaires.

Answer 5

Il s'agit d'un pseudo-code, mais quelque chose comme ceci devrait fonctionner parfaitement.

select *
from table
inner join
(
    select id , max(version_id) maxVersion
    from table 
) dvtbl ON id = dvtbl.id && versionid = dvtbl.maxVersion