UPD: 2017-03-31, la version 5.7.5 de MySQL fait le seul commutateur onl_full_group_by activé par défaut (donc, groupe non-déterministe par requêtes est devenu désactivé). En outre, ils ont mis à jour le groupe par la mise en œuvre et la solution pourrait ne plus fonctionner comme prévu, même avec le commutateur désactivé. On a besoin de vérifier.

la solution de Bill Karwin ci-dessus fonctionne bien lorsque le nombre d'articles dans les groupes est plutôt petit, mais la performance de la requête devient mauvaise lorsque les groupes sont plutôt grands, puisque la solution nécessite environ n*n/2 + n/2 de seulement IS NULL comparaisons.

j'ai fait mes tests sur une table InnoDB de 18684446 lignes avec 1182 groupes. Le tableau contient les résultats d'essais pour les essais de fonctionnement et a le (test_id, request_id) comme clé principale. Ainsi, test_id est un groupe et je cherchais le dernier request_id pour chaque test_id .

la solution de Bill fonctionne déjà depuis plusieurs heures sur mon dell e4310 et je ne sais pas quand elle va finir, même si elle fonctionne sur un indice de couverture (d'où using index dans EXPLAIN).

j'ai quelques autres solutions qui sont basées sur les mêmes idées:

• si l'indice sous-jacent est l'indice BTREE (ce qui est habituellement le cas), la plus grande paire (group_id, item_value) est la dernière valeur à l'intérieur de chaque group_id , qui est le premier pour chaque group_id si nous marchons par l'index dans l'ordre décroissant;
• si nous lisons les valeurs qui sont couverts par un indice, les valeurs sont lues dans l'ordre de l'index;
• chaque indice contient implicitement des colonnes de clés primaires qui y sont annexées (c'est-à-dire que la clé primaire se trouve dans l'indice de couverture). Dans les solutions ci-dessous, j'fonctionner directement sur la clé primaire, dans votre cas, vous aurez juste besoin d'ajouter des colonnes de clé primaire dans la résultat.
• dans de nombreux cas, il est beaucoup moins coûteux d'avoir collecté id de ligne dans l'ordre requis dans une sous-requête et rejoindre le résultat de la sous-requête sur l'id. Puisque pour chaque ligne dans le résultat de la sous-requête MySQL aura besoin d'un seul fetch basé sur la clé primaire, la sous-requête sera mise en premier dans la jointure et les lignes seront sorties dans l'ordre des ids dans la sous-requête (si nous omettons l'ordre explicite par pour la jointure)

3 les voies MySQL utilise des index est un grand article pour comprendre certains détails.

Solution 1

celui-ci est incroyablement rapide, il prend environ 0,8 secondes sur mes 18m + rangées:

SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;

si vous voulez changer l'ordre en ASC, mettez-le dans un sousquery, retourner l'ids seulement et utiliser que comme le sousquery pour joindre au reste des colonnes:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id), request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

celui-ci prend sur les 1,2 secondes sur mes données.

Solution 2

Voici une autre solution qui prend environ 19 secondes pour ma table:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

il renvoie aussi les tests par ordre décroissant. Il est beaucoup plus lent puisqu'il effectue un balayage complet de l'index mais il est ici pour vous donner une idée de la façon de produire N Lignes max pour chaque groupe.

L'inconvénient de la requête est que son résultat ne peut pas être mis en cache par le cache de requêtes.

utilisez votre subquery pour retourner le groupe correct, parce que vous êtes à mi-chemin.

essayez ceci:

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

si ce n'est pas id vous voulez le max de:

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

de cette façon, vous évitez les sous-séries corrélées et/ou l'ordre dans vos sous-séries, qui ont tendance à être très lent/inefficace.

je suis arrivé à une solution différente, qui est d'obtenir les IDs pour le dernier post dans chaque groupe, puis sélectionner à partir de la table messages en utilisant le résultat de la première requête comme argument pour un WHERE x IN construire:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

Je ne sais pas comment cela fonctionne par rapport à certaines autres solutions, mais il a fonctionné de façon spectaculaire pour ma table avec 3+ millions de lignes. (4 seconde d'exécution avec 1200+ résultats)

This devrait fonctionner à la fois sur MySQL et SQL Server.

Solution par la sous-requête violon Lien

select * from messages where id in
(select max(id) from messages group by Name)

Solution Par condition de jointure violon lien

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

la raison de ce post est de donner fiddle link seulement. Même SQL est déjà fourni dans d'autres réponses.

Je n'ai pas encore testé avec grand DB, mais je pense que cela pourrait être plus rapide que de joindre des tables:

SELECT *, Max(Id) FROM messages GROUP BY Name

voici deux suggestions. Tout d'abord, si mysql supporte ROW_NUMBER (), c'est très simple:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

je suppose que par "dernier" vous voulez dire dernier dans L'ordre D'identification. Sinon, changez l'ordre Par clause de la fenêtre ROW_NUMBER() en conséquence. Si ROW_NUMBER () n'est pas disponible, c'est une autre solution:

deuxièmement, si ce n'est pas le cas, c'est souvent une bonne façon de procéder:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

en d'autres termes, sélectionnez messages où il n'est pas un message postérieur-Id avec le même nom.

voici ma solution:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;

Voici une autre façon d'obtenir le dernier enregistrement lié en utilisant GROUP_CONCAT avec commande par et SUBSTRING_INDEX pour choisir un de l'enregistrement de la liste

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

la requête ci-dessus regroupera tous les Other_Columns qui sont dans le même groupe Name et en utilisant ORDER BY id DESC se joindra à tous les Other_Columns dans un groupe spécifique dans l'ordre décroissant avec le séparateur fourni dans mon cas, j'ai utilisé || , en utilisant SUBSTRING_INDEX au-dessus de cette liste choisira le premier

Violon Démo

SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;

essayez ceci:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  

vous pouvez voir d'ici aussi.

http://sqlfiddle.com/#!9 / ef42b / 9

PREMIÈRE SOLUTION

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

DEUXIÈME SOLUTION

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;

y a-t-il un moyen d'utiliser cette méthode pour supprimer les doublons d'un tableau? Le jeu de résultats est essentiellement une collection d'enregistrements uniques, donc si nous pouvions supprimer tous les enregistrements qui ne sont pas dans le jeu de résultats, nous n'aurions effectivement pas de doublons? J'ai essayé mais mySQL a donné une erreur 1093.

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

y a-t-il un moyen de sauvegarder la sortie sur une variable temp puis de supprimer De NOT IN (temp variable)? @Bill merci pour cette solution très utile.

EDIT: Pense que j'ai trouvé la solution:

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);

la requête ci-dessous fonctionnera très bien selon votre question.

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;

Salut @Vijay Dev si votre table messages contient Id qui est incrément automatique de clé primaire, puis à récupérer le dernier enregistrement de base sur la clé primaire de votre requête devrait se lire comme suit:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId

si vous voulez la dernière ligne pour chaque Name , alors vous pouvez donner un numéro de ligne à chaque groupe de ligne par le Name et l'ordre par Id dans l'ordre décroissant.

REQUÊTE

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL Violon

une approche à grande vitesse est la suivante.

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

résultat

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1

Que pensez-vous de ceci:

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

j'ai eu un problème similaire (sur PostgreSQL tough) et sur une table de 1m records. Cette solution prend 1.7 S vs 44s produit par celui avec jointure gauche. Dans mon cas, j'ai dû filtrer le correspondant de votre champ nom contre les valeurs nulles, ce qui a donné des performances encore meilleures de 0,2 secondes

Clairement, il y a beaucoup de façons différentes d'obtenir les mêmes résultats, votre question semble être ce qui est un moyen efficace d'obtenir les derniers résultats de chaque groupe dans MySQL. Si vous travaillez avec d'énormes quantités de données et en supposant que vous utilisez InnoDB avec même les dernières versions de MySQL (comme 5.7.21 et 8.0.4-rc) alors il pourrait ne pas y avoir une façon efficace de le faire.

nous avons parfois besoin de faire cela avec des tableaux avec encore plus de 60 millions de lignes.

dans ces exemples, je vais utiliser les données avec seulement environ 1,5 million de lignes où les requêtes faut trouver des résultats pour tous les groupes de données. Dans nos cas réels, nous aurions souvent besoin de retourner les données d'environ 2 000 groupes (ce qui, hypothétiquement, ne nécessiterait pas l'examen d'une grande partie des données).

j'utiliserai les tableaux suivants:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

la table des températures est peuplée avec environ 1,5 millions de et avec 100 groupes différents. Le groupe selected_group est peuplé de ces 100 groupes (dans nos cas, cela serait normalement inférieur à 20% pour tous les groupes).

comme ces données sont aléatoires, cela signifie que plusieurs lignes peuvent avoir les mêmes dates d'enregistrement. Ce que nous voulons, c'est obtenir une liste de tous les groupes sélectionnés afin de groupID avec la dernière recordedTimestamp pour chaque groupe, et si le même groupe a plus d'une ligne correspondante comme ça, puis la dernière ID correspondant à ces lignes.

si hypothétiquement MySQL avait une fonction last () qui retournait les valeurs de la dernière ligne dans un ordre spécial par clause, alors nous pourrions simplement faire:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

qui n'aurait besoin d'examiner que quelques 100 lignes dans ce cas car il n'utilise aucun des groupes normaux par fonctions. Cela s'exécuterait en 0 secondes et serait donc très efficace. Notez que normalement dans MySQL nous verrons un ordre Par clause suivant le groupe Par clause cependant cet ordre Par clause est utilisé pour déterminer l'ordre de la dernière fonction (), si elle était après le groupe à ce moment-là il serait ordonner les groupes. Si aucun groupe par clause n'est présent, les dernières valeurs seront les mêmes dans toutes les lignes retournées.

cependant MySQL n'a pas cela alors regardons différentes idées de ce qu'il a et prouvons qu'aucun de ceux-ci sont efficaces.

exemple 1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

cela a examiné 3 009 254 lignes et a pris ~0,859 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

cela a examiné 1.505.331 lignes et a pris ~1.25 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

cela a examiné 3 009 685 rangées et a pris ~1,95 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

cela a examiné 6 137 810 rangées et a pris ~2,2 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

cela a examiné 6 017 808 lignes et a pris ~ 4,2 secondes sur 8.0.4-rc

exemple 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

cela a examiné 6 017 908 lignes et a pris ~17,5 secondes sur 8.0.4-rc

exemple 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

celui-ci prenait une éternité donc j'ai dû le tuer.

si la performance est vraiment votre préoccupation, vous pouvez introduire une nouvelle colonne sur la table appelée IsLastInGroup de type BIT.

positionne true sur les colonnes qui sont les dernières et le maintient avec chaque ligne insert/update/delete. Les Écritures seront plus lentes, mais vous profiterez des lectures. Cela dépend de votre cas d'utilisation, et je recommande que si vous avez lu.

donc votre requête ressemblera à:

SELECT * FROM Messages WHERE IsLastInGroup = 1

select * from messages group by name desc