SQL wybierz tylko wiersze z maksymalną wartością w kolumnie

Preferuję użycie jak najmniejszej ilości kodu...

Możesz to zrobić używając IN spróbuj tego:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

Jeszcze innym rozwiązaniem jest użycie skorelowanej subquery:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Posiadanie indeksu w (id,rev) powoduje, że zapytanie podrzędne jest prawie proste...

Poniżej znajdują się porównania do rozwiązań w odpowiedzi @AdrianCarneiro (subquery, leftjoin), opartych na pomiarach MySQL z tabelą InnoDB o wielkości ~1mln rekordów, wielkość grupy wynosi: 1-3.

Podczas gdy Pełne skanowanie tabeli subquery / leftjoin/skorelowane czasy odnoszą się do siebie jako 6/8/9, jeśli chodzi o bezpośrednie wyszukiwanie lub wsad (id in (1,2,3)), subquery jest znacznie wolniejsze niż pozostałe (z powodu ponownego uruchomienia subquery). Jednak nie mogłem odróżnić leftjoin od skorelowanych rozwiązań w szybkości.

Ostatnia uwaga, ponieważ leftjoin tworzy N * (n+1)/2 łączy się w grupy, na jego wydajność może mieć duży wpływ wielkość grup...

Nie mogę ręczyć za wydajność, ale oto sztuczka inspirowana ograniczeniami Microsoft Excel. Ma kilka dobrych cech

GOOD STUFF

• powinno wymusić powrót tylko jednego "rekordu maksymalnego", nawet jeśli jest remis (czasami przydatny)
• nie wymaga połączenia

Podejście

Jest trochę brzydki i wymaga, aby wiedzieć coś o zakresie ważnych wartości rev kolumna. Niech Zakładamy, że znamy rev kolumna jest liczbą od 0,00 do 999 włącznie z dziesiętnymi, ale zawsze będą tylko dwie cyfry na prawo od punktu dziesiętnego (np. 34,17 będzie prawidłową wartością).

Sednem sprawy jest to, że tworzysz pojedynczą kolumnę syntetyczną za pomocą łańcucha łączącego / pakującego podstawowe pole porównania wraz z żądanymi danymi. W ten sposób możesz wymusić funkcję SQL MAX() aggregate, aby zwróciła wszystkie dane (ponieważ ma zapakowane w jedną kolumnę). Następnie musisz rozpakować dane.

Oto jak wygląda powyższy przykład, napisany w 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

Pakowanie zaczyna się od wymuszenia rev kolumna ma być liczbą o znanej długości znaków niezależnie od wartości rev tak, że na przykład

• 3.2.1003.201
• 57.1057.001
• 923.88 1923.881

If you do it right, porównywanie łańcuchów dwóch liczb powinno dawać takie same "max" jak porównywanie liczb dwóch liczb i łatwo jest przekonwertować z powrotem na liczbę oryginalną za pomocą funkcji substring(która jest dostępna w takiej czy innej formie praktycznie wszędzie).

Jestem oszołomiony, że żadna odpowiedź nie oferuje rozwiązania funkcji okna 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 

Dodane w standardzie SQL ANSI/ISO Standard SQL: 2003 i Później rozszerzone o ANSI/ISO Standard SQL: 2008, funkcje window (lub windowing) są teraz dostępne u wszystkich głównych dostawców. Istnieje więcej typów funkcji rank dostępnych w przypadku remisu: RANK, DENSE_RANK, PERSENT_RANK.

Myślę, że jest to najprostsze rozwiązanie:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;

• SELECT*: Return all fields.
• from Employee: Table searched on.
• (Wybierz *...) subquery: zwróć wszystkich ludzi, posortowane według wynagrodzenia.
• GROUP BY employeesub.Pensja:: Wymuś, aby najwyższy, uporządkowany rząd wynagrodzeń każdego pracownika był zwróconym wynikiem.

Jeśli zdarzy ci się potrzebować tylko jednego wiersza, jest to jeszcze łatwiejsze:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

• zamówienie przez pracownika.Pensja DESC: Uporządkuj wyniki według wynagrodzenia, najpierw z najwyższymi pensjami.
• LIMIT 1: zwraca tylko jeden wynik.

Zrozumienie tego podejścia, rozwiązanie każdego z tych podobnych problemów staje się trywialne: dostać pracownika z NAJNIŻSZYM wynagrodzeniem( Zmień DESC na ASC), uzyskać najlepszych dziesięciu zarabiających pracowników( Zmień LIMIT 1 do limitu 10), sortować za pomocą innego pola (Zmień kolejność według pracownika.Wynagrodzenie na zlecenie pracownika.Prowizji), itp..

Coś takiego?

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)

Ponieważ jest to najbardziej popularne pytanie w odniesieniu do tego problemu, zamieszczę ponownie kolejną odpowiedź na to pytanie również tutaj:

Wygląda na to, że jest prostszy sposób na to (ale tylko w MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Proszę o odpowiedź użytkownika Bohemian w to pytanie za udzielenie tak zwięzłej i eleganckiej odpowiedzi na ten problem.

EDIT: chociaż to rozwiązanie działa dla wielu osób to może nie być stabilne na dłuższą metę, ponieważ MySQL nie gwarancja, że instrukcja GROUP BY zwróci znaczące wartości dla kolumn nie znajdujących się na liście GROUP BY. Więc używaj tego rozwiązania na własne ryzyko

Lubię używać rozwiązania NOT EXIST na ten problem:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

Trzecie rozwiązanie, o którym prawie nigdy nie wspominałem, jest specyficzne dla MySQL i wygląda tak:

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

Tak wygląda okropnie (konwersja na string i back itp.), ale z mojego doświadczenia wynika, że jest to zwykle szybsze od innych rozwiązań. Może to tylko dla moich przypadków użycia, ale używałem go na stołach z milionami rekordów i wieloma unikalnymi identyfikatorami. Może dlatego, że MySQL jest dość kiepski w optymalizacji innych rozwiązań(przynajmniej w 5.0 dni, kiedy wpadłem na to rozwiązanie).

One ważne jest to, że GROUP_CONCAT ma maksymalną długość Dla ciągu, który może budować. Prawdopodobnie chcesz zwiększyć ten limit, ustawiając zmienną group_concat_max_len. Pamiętaj, że będzie to limit skalowania, jeśli masz dużą liczbę wierszy.

W każdym razie powyższe nie działa bezpośrednio, jeśli twoje pole zawartości jest już tekstem. W takim przypadku prawdopodobnie chcesz użyć innego separatora, np. \0. Możesz również szybciej napotkać group_concat_max_len limit.

Jeśli masz wiele pól w instrukcji select i chcesz uzyskać najnowszą wartość dla wszystkich tych pól za pomocą zoptymalizowanego kodu:

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

A może tak:

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

Użyłbym tego:

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 nie jest zbyt wydajne, ale w klauzuli JOIN wydaje się być użyteczna. Nie jestem ekspertem w optymalizacji zapytań, ale próbowałem w MySQL, PostgreSQL, FireBird i działa bardzo dobrze.

Można używać tego schematu w wielu połączeniach i z klauzulą WHERE. Jest to mój przykład pracy (rozwiązanie identycznego do twojego problemu z tabelą "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'

Jest pytany na tablicach mających tak wiele rekordów, że zajmuje mniej niż 0,01 sekundy na naprawdę niezbyt mocnej maszynie.

Nie używałbym klauzuli in (Jak to jest wspomniane gdzieś powyżej). IN jest podane do użycia z krótkimi listami constans, a nie jako filtr zapytań zbudowany na subquery. Dzieje się tak dlatego, że zapytania podrzędne w IN są wykonywane dla każdego zeskanowanego rekordu, który może wykonać zapytanie zajmując bardzo loooong czasu.

Nie mySQL, ale dla innych osób szukających tego pytania i korzystających z SQL, innym sposobem rozwiązania problemu greatest-n-per-group jest użycie Cross Apply w 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

Oto przykład w SqlFiddle

To rozwiązanie sprawia, że tylko jeden wybór z YourTable, dlatego jest szybszy. Działa tylko dla MySQL i SQLite (dla SQLite Usuń DESC) zgodnie z testem na sqlfiddle.com. może da się go dostosować do pracy na innych językach, których nie znam.

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

Oto dobry sposób na to

Użyj następującego kodu:

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)

Lubię to robić poprzez ranking rekordów według jakiejś kolumny. W tym przypadku wartości rank rev pogrupowane według id. Ci z wyższymi rev będą mieli niższe rankingi. Tak więc najwyższy rev będzie miał rangę 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;

Nie jestem pewien, czy wprowadzenie zmiennych spowalnia całość. Ale przynajmniej nie pytam dwa razy.

Jeśli ktoś szuka LINQ verson, to chyba działa na mnie:

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

Posortowano pole rev w odwrotnej kolejności, a następnie pogrupowano według id, które dało pierwszy wiersz każdej grupy, który ma najwyższą wartość rev.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Testowane w http://sqlfiddle.com / z następującymi danymi

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

Dało to następujący wynik w MySql 5.5 i 5.6

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

Oto inne rozwiązanie mam nadzieję, że komuś pomoże

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

Żadna z tych odpowiedzi nie zadziałała.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Wybierz * Od pracownika gdzie pracownik.Wynagrodzenie w (select max (pensja) from Employe_id group by Employe_id) Zamówienie przez pracownika.Wynagrodzenie

Oto inne rozwiązanie pobierania rekordów tylko z polem, które ma maksymalną wartość dla tego pola. To działa dla sql400, który jest platformą, na której pracuję. W tym przykładzie rekordy z maksymalną wartością w polu FIELD5 zostaną pobrane za pomocą następującego polecenia SQL.

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)

Użyłem poniższego, aby rozwiązać własny problem. Najpierw utworzyłem tabelę tymczasową i wstawiłem wartość max rev na unikalny identyfikator.

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

Następnie dołączyłem te maksymalne wartości (#temp1) do wszystkich możliwych kombinacji id/content. W ten sposób naturalnie odfiltrowuję Nie-maksymalne kombinacje id/content i pozostaję z jedynymi wartościami max rev dla każdej z nich.

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

Innym sposobem wykonania zadania jest użycie funkcji analitycznej MAX() w klauzuli 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 

Inne rozwiązanie nad partycją już udokumentowane w tym poście to

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 

Możesz dokonać wyboru bez łączenia, gdy połączysz rev i id w jedną maxRevId wartość dla MAX(), a następnie podzielisz ją z powrotem na oryginalne wartości:

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;

Jest to szczególnie szybkie, gdy istnieje złożone połączenie zamiast pojedynczej tabeli. Przy tradycyjnym podejściu połączenie złożone odbywałoby się dwukrotnie.

Powyższa kombinacja jest prosta Z FUNKCJAMI bitowymi, gdy rev i id są INT UNSIGNED (32-bitowe), a łączna wartość pasuje do BIGINT UNSIGNED (64-bitowe). Gdy id & rev są większe niż wartości 32-bitowe lub złożone z wielu kolumn, musisz połączyć wartość w np. wartość binarną z odpowiednim wypełnieniem dla MAX().

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

To mi działa w sqlite3:

SELECT *, MAX(rev) FROM t1 GROUP BY id

Z * otrzymujesz duplikat kolumny rev, ale to nie jest duży problem.

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;