SELECT DISTINCT ON (expr) ... liefert eine Zeile für jeden unterschiedlichen Wert von expr: die erste Zeile dieser Gruppe in der Reihenfolge von ORDER BY. Sortiere zuerst nach dem Gruppenschlüssel und dann nach dem, was „zuerst“ bestimmt. Für die neueste Bestellung jedes Kunden ist das ORDER BY customer, created_at DESC.
Die Abfrage liefert drei Zeilen: Bestellung 2 für asha, 4 für ben und 5 für chen, jeweils die neueste. DISTINCT ON ist eine Erweiterung von PostgreSQL. Andere Datenbanken brauchen die Form mit Window Functions weiter unten.
Wie ORDER BY entscheidet, welche Zeile bleibt
PostgreSQL sortiert die Zeilen und behält dann jedes Mal die erste Zeile, wenn sich der Wert von DISTINCT ON ändert. Daraus folgt:
- Das
ORDER BYmuss mit den Ausdrücken ausDISTINCT ONbeginnen, sonst meldet PostgreSQL "SELECT DISTINCT ON expressions must match initial ORDER BY expressions". - Ohne ein
ORDER BYüber diese Ausdrücke hinaus ist nicht vorhersehbar, welche Zeile bleibt, und das kann sich von Lauf zu Lauf ändern.
Ersetze created_at DESC durch total DESC, und dieselbe Abfrage liefert die größte Bestellung jedes Kunden:
Jetzt ist ashas Zeile Bestellung 1 (40) und bens Zeile Bestellung 3 (60).
Gleichstände bewusst auflösen
Haben zwei Zeilen denselben Wert in der Sortierspalte, kann jede gewinnen. Füge als letzten Sortierschlüssel etwas Eindeutiges hinzu, etwa den Primärschlüssel, damit das Ergebnis jedes Mal gleich ist:
Die Zeilen 2 und 3 haben beide 95 Punkte. Mit id als letztem Schlüssel gewinnt Zeile 2.
Nach mehr als einer Spalte gruppieren
Gib mehrere Ausdrücke an, um eine Zeile pro Kombination zu bekommen:
Jedes Paar aus Sensor und Messart behält seinen neuesten Messwert: s1 humidity 40, s1 temp 22, s2 temp 19.
Die portable Variante mit ROW_NUMBER
ROW_NUMBER() nummeriert die Zeilen innerhalb jeder Partition. Wer Nummer 1 behält, bekommt dasselbe Ergebnis wie mit DISTINCT ON, und das funktioniert auch in MySQL 8, SQL Server und SQLite:
SELECT customer, id, total, created_at
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Nimm diese Form, wenn du die ersten n pro Gruppe brauchst (rn <= 3) oder wenn die Abfrage auf einer anderen Datenbank laufen muss. Die Seite zu Window Functions erklärt PARTITION BY.
Schnell machen
DISTINCT ON sortiert die Eingabe. Gib ihm auf einer großen Tabelle also einen Index, der zum ORDER BY passt:
CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);
PostgreSQL kann die Zeilen dann in der richtigen Reihenfolge lesen, statt sie zu sortieren. Bei wenigen Gruppen mit vielen Zeilen pro Gruppe liest ein LATERAL Join, der pro Kunde LIMIT 1 aus demselben Index holt, meist weniger Zeilen. Die Seite zu Joins zeigt LATERAL. Vergleiche beides mit EXPLAIN ANALYZE auf deinen Daten.
Häufig gestellte Fragen
Was ist der Unterschied zwischen DISTINCT und DISTINCT ON?
DISTINCT entfernt Zeilen, die in jeder ausgewählten Spalte gleich sind. DISTINCT ON (a) behält eine Zeile für jeden Wert von a, und die übrigen Spalten stammen aus der Zeile, die in dieser Gruppe zuerst sortiert wird.
Ist DISTINCT ON Standard-SQL?
Nein. Es ist eine Erweiterung von PostgreSQL und funktioniert daher nicht in MySQL oder SQL Server. Die portable Variante ist ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) in einer Unterabfrage, gefiltert auf Zeilennummer 1.
Warum bekomme ich "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?
Das ORDER BY muss mit denselben Ausdrücken wie DISTINCT ON beginnen, in derselben Reihenfolge. Schreib DISTINCT ON (customer) ... ORDER BY customer, created_at DESC und nicht nur ORDER BY created_at DESC.
Kann DISTINCT ON die ersten 3 Zeilen pro Gruppe liefern?
Nein, es liefert genau eine Zeile pro Gruppe. Für die ersten n nummerierst du die Zeilen mit ROW_NUMBER() und behältst rn <= 3, oder du nimmst eine LATERAL Unterabfrage mit LIMIT 3.