upd: 2017-03-31, Wersja 5.7.5 MySQL sprawił, że przełącznik ONLY_FULL_GROUP_BY był domyślnie włączony (stąd nie-deterministyczna Grupa zapytań została wyłączona). Co więcej, zaktualizowali grupę według wdrożenia, a rozwiązanie może nie działać zgodnie z oczekiwaniami nawet przy wyłączonym przełączniku. Trzeba to sprawdzić.

Powyższe rozwiązanie Billa Karwina działa dobrze, gdy liczba elementów w grupach jest raczej mała, ale wydajność zapytania staje się zła, gdy grupy są dość duże, ponieważ rozwiązanie wymaga około n*n/2 + n/2 Tylko IS NULL porównań.

Wykonałem testy na tablicy InnoDB 18684446 wierszy z 1182 grupami. Tabela zawiera wyniki testów dla testów funkcjonalnych i ma (test_id, request_id) jako klucz podstawowy. Tak więc test_id jest grupą i szukałem ostatniej request_id dla każdej test_id.

• jeśli indeks bazowy jest indeksem BTREE (co zwykle ma miejsce), największa para (group_id, item_value) jest ostatnią wartością w każdym group_id, to jest pierwsza dla każdego group_id jeśli przechodzimy przez indeks w porządku malejącym;
• jeśli odczytamy wartości, które są objęte indeksem, wartości są odczytywane w kolejności indeksu;
• każdy indeks domyślnie zawiera kolumny klucza podstawowego dołączone do tego (czyli klucz podstawowy znajduje się w indeksie pokrycia). W poniższych rozwiązaniach operuję bezpośrednio na kluczu podstawowym, w Twoim przypadku wystarczy dodać kolumny klucza podstawowego w wyniku.
• w wielu przypadkach znacznie taniej jest zebrać wymagane identyfikatory wierszy w wymaganej kolejności w zapytaniu podrzędnym i dołączyć wynik zapytania podrzędnego do id. Ponieważ dla każdego wiersza w wyniku zapytania podrzędnego MySQL będzie potrzebował jednego pobierania opartego na kluczu podstawowym, podquery będą umieszczane jako pierwsze w join, a wiersze będą wyświetlane w kolejności ID w podquery (jeśli pominiemy jawną kolejność przez dla join)

3 ways MySQL uses indexes to świetny artykuł, aby zrozumieć niektóre szczegóły.

Rozwiązanie 1

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

Jeśli chcesz zmienić kolejność na ASC, umieść ją w zapytaniu podrzędnym, zwróć tylko identyfikatory i użyj tego jako podquery do dołączenia do reszty kolumn:

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;

Ten zajmuje około 1,2 sekundy na moich danych.

Rozwiązanie 2

Oto inne rozwiązanie, które zajmuje około 19 sekund dla mojego stołu:

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

Zwraca również testy w kolejności malejącej. Jest znacznie wolniejszy, ponieważ wykonuje pełne skanowanie indeksów, ale jest tutaj, aby dać ci pomysł, jak wypisać N maksymalnych wierszy dla każdej grupy.

Wadą zapytania jest to, że jego wynik nie może być buforowany przez bufor zapytań.

Użyj subquery , aby zwrócić poprawną grupę, ponieważ jesteś w połowie drogi.

Spróbuj tego:

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

Jeśli nie id chcesz max:

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

W ten sposób unikasz skorelowanych zapytań podrzędnych i / lub porządkowania w swoich zapytaniach podrzędnych, które wydają się być bardzo powolne / nieefektywne.

Doszedłem do innego rozwiązania, które polega na pobraniu identyfikatorów dla ostatniego posta w każdej grupie, a następnie wybraniu z tabeli wiadomości, używając wyniku z pierwszego zapytania jako argumentu dlaWHERE x IN konstruktu:

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

Nie wiem, jak to działa w porównaniu z innymi rozwiązaniami, ale działało spektakularnie dla mojej tabeli z 3 + milionami wierszy. (4 sekunda wykonania z 1200 + wyników)

powinno to działać zarówno na MySQL jak i SQL Server.

Rozwiązanie przez sub query fiddle Link

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

Rozwiązanie przez warunek join fiddle link

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

Powodem tego postu jest podanie tylko linku. Ten sam SQL jest już dostępny w innych odpowiedziach.

Jeszcze nie testowałem z dużym DB, ale myślę, że może to być szybsze niż łączenie tabel:

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

Oto dwie propozycje. Po pierwsze, jeśli mysql obsługuje ROW_NUMBER (), jest to bardzo proste:

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;

Po drugie, jeśli nie, to często jest to dobry sposób postępowania:

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

Innymi słowy, wybierz Wiadomości, w których nie ma później-ID wiadomości o tej samej nazwie.

Oto moje rozwiązanie:

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

Oto inny sposób na uzyskanie ostatniego powiązanego rekordu za pomocą {[1] } z order by i SUBSTRING_INDEX, aby wybrać jeden z rekordów z listy

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

Powyższe zapytanie grupuje wszystkie Other_Columns, które są w tej samej grupie Name i używając ORDER BY id DESC połączy wszystkie Other_Columns w określonej grupie w porządku malejącym z podanym separatorem w moim przypadku użyłem ||, używając SUBSTRING_INDEX na tej liście wybierze pierwszą

Demo Fiddle

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

Spróbuj tego:

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 )  

Stąd też można podziwiać widoki.

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

PIERWSZE ROZWIĄZANIE

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

DRUGIE ROZWIĄZANIE

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

Czy możemy użyć tej metody do usunięcia duplikatów w tabeli? Zestaw wyników jest w zasadzie zbiorem unikalnych rekordów, więc gdybyśmy mogli usunąć wszystkie rekordy Nie w zestawie wyników, nie mielibyśmy żadnych duplikatów? Próbowałem tego, ale mySQL dał błąd 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)

Czy istnieje sposób, aby zapisać wyjście do zmiennej temp, a następnie usunąć z NOT in (zmienna temp)? @ Bill dzięki za bardzo przydatne rozwiązanie.

EDIT: chyba znalazłem rozwiązanie:

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

Poniższe zapytanie będzie działać dobrze, jak na twoje pytanie.

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;

Hi @ Vijay Dev jeśli twoja tabela messages zawiera Id, który jest automatycznym przyrostem klucza podstawowego, to aby pobrać najnowszy rekord na podstawie klucza podstawowego, Twoje zapytanie powinno wyglądać następująco:

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

Jeśli chcesz, aby ostatni wiersz dla każdego Name, możesz nadać numer wiersza każdej grupie wierszy przez Name i kolejność przez Id w porządku malejącym.

Zapytanie

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

Podejście ze znaczną prędkością jest następujące.

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

Wynik

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

A może tak:

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

Miałem podobny problem (na PostgreSQL tough) i na tabeli rekordów 1M. To rozwiązanie trwa 1.7 s vs 44s produkowane przez ten z lewym złączem. W moim przypadku musiałem filtrować corrispondant twojego pola name względem wartości NULL, co skutkowało jeszcze lepszymi wynikami o 0.2 sek

Oczywiście istnieje wiele różnych sposobów uzyskania tych samych wyników, twoje pytanie wydaje się być skutecznym sposobem uzyskania ostatnich wyników w każdej grupie w MySQL. Jeśli pracujesz z ogromną ilością danych i zakładając, że używasz InnoDB nawet z najnowszymi wersjami MySQL (takimi jak 5.7.21 i 8.0.4-RC), może to nie być skuteczny sposób na zrobienie tego.

Czasami musimy to zrobić z tabelami zawierającymi nawet ponad 60 milionów wierszy.

Dla tych przykłady użyję danych z tylko około 1,5 miliona wierszy, w których zapytania będą musiały znaleźć wyniki dla wszystkich grup w danych. W naszych rzeczywistych przypadkach często musielibyśmy zwrócić dane z około 2000 grup (które hipotetycznie nie wymagałyby zbadania bardzo dużo danych).

Użyję następujących tabel:

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

Tabela temperatury jest wypełniona około 1,5 miliona przypadkowych rekordów i 100 różnych grup. Selected_group jest wypełniona tych 100 grup (w naszych przypadkach byłoby to zwykle mniej niż 20% dla wszystkich grup).

Ponieważ te dane są losowe, oznacza to, że wiele wierszy może mieć te same rekordy czasu. Chcemy uzyskać listę wszystkich wybranych grup w kolejności groupID z ostatnim zarejestrowanym znacznikiem czasu dla każdej grupy, a jeśli ta sama grupa ma więcej niż jeden pasujący wiersz, taki jak ten, to ostatni pasujący identyfikator tych wierszy.

Jeśli hipotetycznie MySQL miał funkcję last (), która zwracała wartości z ostatniego wiersza w klauzuli special ORDER BY możemy po prostu zrobić:

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;

Który musiałby zbadać tylko kilka 100 wierszy w tym przypadku, ponieważ nie używa żadnej z normalnych funkcji GROUP BY. Wykonałoby się to w ciągu 0 sekund, a tym samym byłoby wysoce wydajne. Zauważ, że normalnie w MySQL widzimy klauzulę ORDER BY następującą po klauzuli GROUP BY, jednak ta klauzula ORDER BY jest używana do określenia kolejności dla funkcji last (), jeśli była po grupie by wtedy to zamawiaj grupy. Jeśli nie ma klauzuli GROUP BY, to ostatnie wartości będą takie same we wszystkich zwracanych wierszach.

Jednak MySQL tego nie ma, więc spójrzmy na różne pomysły na to, co ma i udowodnijmy, że żadna z nich nie jest wydajna.

Przykład 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
);

To zbadało 3,009,254 wierszy i zajęło ~0,859 sekundy na 5.7.21 i nieco dłużej na 8.0.4-rc

Przykład 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;

To zbadało 1,505,331 wierszy i trwało ~1.25 sekund na 5.7.21 i nieco dłużej na 8.0.4-rc

Przykład 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;

To zbadało 3,009,685 wierszy i zajęło ~1,95 sekundy na 5.7.21 i nieco dłużej na 8.0.4-rc

Przykład 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
    )
);

To zbadało 6,137,810 wierszy i zajęło ~2,2 sekundy na 5.7.21 i nieco dłużej na 8.0.4-rc

Przykład 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;

To zbadało 6,017,808 wierszy i zajęło ~4,2 sekundy na 8.0.4-rc

Przykład 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;

To zbadało 6,017,908 wierszy i zajęło ~17.5 sekund na 8.0.4-rc

Przykład 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;

Jeśli wydajność jest naprawdę Twoim zmartwieniem, możesz wprowadzić nową kolumnę w tabeli o nazwie IsLastInGroup Typu BIT.

Ustaw na true w kolumnach, które są ostatnie i utrzymuj je z każdym wierszem insert/update / delete. Pisanie będzie wolniejsze, ale skorzystasz na czytaniu. To zależy od Twojego przypadku użycia i polecam go tylko wtedy, gdy jesteś skoncentrowany na czytaniu.

Więc twoje zapytanie będzie wyglądało następująco:

SELECT * FROM Messages WHERE IsLastInGroup = 1

select * from messages group by name desc