mysql - spalten - sql zeile mit höchstem wert ausgeben




SQL wählt nur Zeilen mit maximalem Wert für eine Spalte aus (20)

Ich habe diese Tabelle für Dokumente (vereinfachte Version hier):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Wie wähle ich eine Zeile pro ID und nur die größte rev aus?
Mit den obigen Daten sollte das Ergebnis zwei Zeilen enthalten: [1, 3, ...] und [2, 1, ..] . Ich benutze MySQL .

Derzeit verwende ich Prüfungen in der while Schleife, um alte Drehzahlen aus dem Resultset zu erkennen und zu überschreiben. Aber ist dies die einzige Methode, um das Ergebnis zu erreichen? Gibt es keine SQL- Lösung?

Aktualisieren
Wie die Antworten nahelegen, gibt es eine SQL-Lösung und hier eine SQLFiddle-Demo .

Update 2
Ich bemerkte, dass nach dem Hinzufügen der oben genannten SQLFiddle die Rate, mit der die Frage hochgestuft wurde, die Upvote-Rate der Antworten übertroffen hat. Das war nicht die Absicht! Die Geige basiert auf den Antworten, insbesondere der angenommenen Antwort.


Auf den ersten Blick...

Sie benötigen GROUP BY eine GROUP BY Klausel mit der MAX Aggregatfunktion:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Es ist nie so einfach, oder?

Ich habe gerade bemerkt, dass du auch die content brauchst.

Dies ist eine sehr häufige Frage in SQL: Finde die ganzen Daten für die Zeile mit einem maximalen Wert in einer Spalte für eine Gruppenidentifizierung. Das habe ich während meiner Karriere oft gehört. Eigentlich war es eine der Fragen, die ich im technischen Interview meines aktuellen Jobs beantwortet habe.

Tatsächlich ist es so üblich, dass die -Community ein einziges Tag erstellt hat, um sich mit Fragen wie dieser zu befassen: greatest-n-per-group .

Im Grunde haben Sie zwei Ansätze, um dieses Problem zu lösen:

Beitritt mit einfacher group-identifier, max-value-in-group Sub-Abfrage

Bei diesem Ansatz finden Sie zuerst den group-identifier, max-value-in-group (oben bereits gelöst) in einer Unterabfrage. Dann verbinden Sie Ihre Tabelle mit der Unterabfrage mit Gleichheit auf group-identifier und max-value-in-group :

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Linke Join mit Self, Tweaking Join Bedingungen und Filter

Bei dieser Vorgehensweise verlassen Sie die Tabelle mit sich selbst. Gleichheit geht natürlich in der group-identifier . Dann, 2 intelligente Bewegungen:

  1. Die zweite Join-Bedingung hat den linken Seitenwert kleiner als den rechten Wert
  2. Wenn Sie Schritt 1 ausführen, wird die Zeile (n), die tatsächlich den Maximalwert hat, auf der rechten Seite NULL haben (es handelt sich um einen LEFT JOIN , erinnern Sie sich?). Dann filtern wir das verbundene Ergebnis und zeigen nur die Zeilen an, deren rechte Seite NULL .

Also hast du am Ende:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Fazit

Beide Ansätze bringen genau das gleiche Ergebnis.

Wenn Sie zwei Zeilen mit dem group-identifier max-value-in-group , sind beide Zeilen im Ergebnis beider Ansätze enthalten.

Beide Ansätze sind SQL ANSI-kompatibel und funktionieren daher mit Ihrem bevorzugten RDBMS, unabhängig von seinem "Geschmack".

Beide Ansätze sind auch leistungsfreundlich, jedoch kann Ihre Laufleistung variieren (RDBMS, DB-Struktur, Indizes usw.). Also, wenn Sie einen Ansatz gegenüber dem anderen wählen, Benchmark . Und stellen Sie sicher, dass Sie diejenige auswählen, die für Sie am sinnvollsten ist.


Da dies die populärste Frage in Bezug auf dieses Problem ist, werde ich hier noch eine weitere Antwort darauf posten:

Es sieht so aus, als ob es einen einfacheren Weg gibt (aber nur in MySQL ):

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

Bitte kreuzen Sie die Antwort des Benutzers Bohemian in dieser Frage an, um so eine prägnante und elegante Antwort auf dieses Problem zu geben.

BEARBEITEN: Obwohl diese Lösung für viele Menschen funktioniert, ist sie auf lange Sicht möglicherweise nicht stabil, da MySQL nicht garantiert, dass die GROUP BY-Anweisung sinnvolle Werte für Spalten zurückgibt, die nicht in der GROUP BY-Liste enthalten sind. Verwenden Sie diese Lösung auf eigene Gefahr


Diese Lösung macht nur eine Auswahl von YourTable, daher ist es schneller. Es funktioniert nur für MySQL und SQLite (für SQLite entfernen DESC) nach Test auf sqlfiddle.com. Vielleicht kann es optimiert werden, um an anderen Sprachen zu arbeiten, mit denen ich nicht vertraut bin.

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

Eine dritte Lösung, die ich kaum jemals erwähnt habe, ist MySQL-spezifisch und sieht so aus:

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

Ja, es sieht schrecklich aus (Umwandlung in String und zurück usw.), aber nach meiner Erfahrung ist es normalerweise schneller als die anderen Lösungen. Vielleicht nur für meine Anwendungsfälle, aber ich habe es auf Tabellen mit Millionen von Datensätzen und vielen einzigartigen IDs verwendet. Vielleicht liegt es daran, dass MySQL ziemlich schlecht darin ist, die anderen Lösungen zu optimieren (zumindest in den 5.0 Tagen, als ich mit dieser Lösung aufkam).

Eine wichtige Sache ist, dass GROUP_CONCAT eine maximale Länge für die Zeichenfolge hat, die es aufbauen kann. Wahrscheinlich möchten Sie dieses Limit erhöhen, indem Sie die Variable group_concat_max_len . Beachten Sie, dass dies eine Skalierungsgrenze darstellt, wenn Sie eine große Anzahl von Zeilen haben.

Wie auch immer, das obige funktioniert nicht direkt, wenn Ihr Inhaltsfeld bereits Text ist. In diesem Fall möchten Sie wahrscheinlich ein anderes Trennzeichen verwenden, wie zB \ 0. Sie werden auch schneller auf das group_concat_max_len Limit group_concat_max_len .


Eine weitere Möglichkeit, den Job auszuführen, ist die Verwendung der analytischen Funktion MAX () in der OVER PARTITION-Klausel

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 

Die andere OVER PARTITION-Lösung, die bereits in diesem Post dokumentiert ist, ist

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 

Diese 2 SELECT funktionieren gut auf Oracle 10g.


Etwas wie das?

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)

Hier ist eine andere Lösung zum Abrufen der Datensätze nur mit einem Feld, das den maximalen Wert für dieses Feld hat. Dies funktioniert für SQL400, die Plattform, an der ich arbeite. In diesem Beispiel werden die Datensätze mit dem Höchstwert in Feld FIELD5 von der folgenden SQL-Anweisung abgerufen.

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)

Hier ist eine nette Art, das zu tun

Verwenden Sie folgenden Code:

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)

Ich bin verblüfft, dass keine Antwort SQL-Fensterfunktionslösung angeboten hat:

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 

Hinzugefügt im SQL-Standard ANSI / ISO Standard SQL: 2003 und später erweitert mit ANSI / ISO Standard SQL: 2008, Fenster- (oder Windowing-) Funktionen sind jetzt bei allen wichtigen Herstellern verfügbar. Es gibt mehr Arten von Rangfunktionen, die verfügbar sind, um mit einem Gleichstandproblem umzugehen: RANK, DENSE_RANK, PERSENT_RANK .


Ich denke, das ist die einfachste Lösung:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: Alle Felder zurückgeben.
  • FROM Employee: Tabelle gesucht.
  • (SELECT * ...) Unterabfrage: Zurückgeben aller Personen, sortiert nach Gehalt.
  • GROUP BY employeesub.Salary:: Erzwingt die Top-sortierte Gehaltszeile jedes Mitarbeiters als zurückgegebenes Ergebnis.

Wenn Sie nur die eine Zeile benötigen, ist es noch einfacher:

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

Ich denke auch, dass es am einfachsten ist, zu anderen Zwecken zu brechen, zu verstehen und zu modifizieren:

  • ORDER BY Employee.Salary DESC: Ordnen Sie die Ergebnisse nach dem Gehalt an, mit den höchsten Gehältern zuerst.
  • LIMIT 1: Gibt nur ein Ergebnis zurück.

Diesen Ansatz zu verstehen und all diese ähnlichen Probleme zu lösen, wird trivial: Mitarbeiter mit dem niedrigsten Gehalt bekommen (DESC in ASC ändern), Top-Ten-Mitarbeiter verdienen (LIMIT 1 zu LIMIT 10 ändern), nach einem anderen Feld sortieren (ORDER BY ändern) Employee.Salary zu ORDER BY Employee.Commission), etc ..


Ich kann mich nicht für die Leistung verbürgen, aber hier ist ein Trick, der von den Einschränkungen von Microsoft Excel inspiriert ist. Es hat einige gute Eigenschaften

GUTES ZEUG

  • Es sollte die Rückkehr von nur einem "Max Record" erzwingen, auch wenn es eine Gleichheit gibt (manchmal nützlich)
  • Es erfordert keinen Beitritt

ANSATZ

Es ist ein bisschen hässlich und erfordert, dass Sie etwas über den Bereich der gültigen Werte der Rev- Spalte wissen. Nehmen wir an, wir wissen, dass die rev- Spalte eine Zahl zwischen 0.00 und 999 einschließlich Dezimalstellen ist, dass aber immer nur zwei Stellen rechts vom Dezimalpunkt stehen (z. B. 34.17 wäre ein gültiger Wert).

Der Kern der Sache ist, dass Sie eine einzelne synthetische Spalte durch Verketten / Verpacken des primären Vergleichsfeldes zusammen mit den gewünschten Daten erstellen. Auf diese Weise können Sie die SQL-Funktion MAX () zwingen, alle Daten zurückzugeben (weil sie in eine einzelne Spalte gepackt wurde). Dann müssen Sie die Daten entpacken.

So sieht es mit dem obigen Beispiel aus, geschrieben in 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

Das Packen beginnt damit, dass die rev- Spalte unabhängig vom Wert von rev zum Beispiel eine Anzahl bekannter Zeichenlängen ist

  • 3.2 wird 1003.201
  • 57 wird 1057.001
  • 923.88 wird 1923.881

Wenn Sie es richtig machen, sollte der String-Vergleich zweier Zahlen das gleiche "max" ergeben wie der numerische Vergleich der beiden Zahlen und es ist einfach, mit der Teilstring-Funktion (die in der einen oder anderen Form verfügbar ist) zurück in die ursprüngliche Zahl zu konvertieren überall).


Ich mache das gerne, indem ich die Datensätze nach einer Spalte sortiere. In diesem Fall werden die rev Werte nach id gruppiert. Diejenigen mit höherem rev werden niedrigere Platzierungen haben. Die höchste rev hat also den 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;

Nicht sicher, ob die Einführung von Variablen das Ganze langsamer macht. Aber ich YOURTABLE zweimal.


Ich würde das verwenden:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Unterabfrage SELECT ist vielleicht nicht zu effizient, aber in JOIN scheint die Klausel verwendbar zu sein. Ich bin kein Experte in der Optimierung von Abfragen, aber ich habe es bei MySQL, PostgreSQL, FireBird versucht und es funktioniert sehr gut.

Sie können dieses Schema in mehreren Joins und mit WHERE-Klausel verwenden. Es ist mein Arbeitsbeispiel (Lösung identisch mit Ihrem Problem mit Tabelle "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'

Es wird auf den Tabellen gefordert, die Teenager sounds der Aufzeichnungen haben, und es dauert weniger als 0,01 Sekunden auf wirklich nicht zu stark die Maschine.

Ich würde die IN-Klausel nicht verwenden (wie oben erwähnt). IN wird verwendet, um mit kurzen Listen von Konstanten zu verwenden, und nicht als Abfragefilter, der auf Unterabfrage aufgebaut ist. Dies liegt daran, dass die Unterabfrage in IN für jeden gescannten Datensatz durchgeführt wird, wodurch die Abfrage sehr lange dauern kann.


Keine dieser Antworten hat für mich funktioniert.

Das hat bei mir funktioniert.

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

Sortierte das rev-Feld in umgekehrter Reihenfolge und gruppierte dann nach ID, was die erste Zeile jeder Gruppierung ergab, die die mit dem höchsten rev-Wert ist.

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

Getestet in http://sqlfiddle.com/ mit den folgenden Daten

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

Dies ergab das folgende Ergebnis in MySql 5.5 und 5.6

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

Viele, wenn nicht alle der anderen Antworten sind für kleine Datensätze geeignet. Für die Skalierung ist mehr Vorsicht geboten. Siehe here .

Es beschreibt mehrere schnellere Möglichkeiten, gruppenweise max und top-N pro Gruppe auszuführen.


Wenn jemand nach einer Linq-Version sucht, scheint dies für mich zu funktionieren:

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

Wie wäre es damit:

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

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

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




greatest-n-per-group