Bei "doppelten" Datensätzen nur einen anzeigen

[Falk Prüfer]

Da sich das Thema allgemeiner Beliebtheit erfreut, hier der ultimative Beitrag zum Thema "Wie gelingt es mir mit SQL, aus einer Menge gleicher Daten, nur den Jüngsten auszuwählen?"

Gegeben sei folgende Tabelle:
[highlight=sql]
create table doppeltest (
id integer(8) not null auto_increment,
name varchar(64) not null,
last_use datetime not null,
constraint primary key (id)
);
[/highlight]
mit folgenden Daten:
[highlight=sql]
insert into doppeltest (name, last_use) values
('Otto', '2010-07-01 20:30:24'),
('Kurt', '2010-07-01 21:42:54'),
('Karl', '2010-07-01 18:21:31'),
('Otto', '2010-07-01 19:24:29'),
('Kurt', '2010-07-01 10:33:22'),
('Max', '2010-07-01 09:36:54'),
('Otto', '2010-07-01 21:40:27');
[/highlight]

Es sollen alle Datensätze mit "id", "name" und "last_use" aufgelistet werden, jedoch für jeden "name" nur der mit dem jüngsten "last_use". Da ausdrücklich auch eine Auflistung von "id" gefordert ist, scheidet eine einfache Gruppierung und Verwendung der Aggregatfunktion MAX() für "last_use" aus.
Die Lösung liegt in der Abfrage mittels Subselect. Jedes "last_use" wird dabei mit dem jüngsten "date" jeder mittels "name" gebildeten Gruppe verglichen.

[highlight=sql]
select a.id, a.name, a.last_use
from doppeltest a
where a.last_use = (
select max(b.last_use)
from doppeltest b
where b.name = a.name
)
[/highlight]
Ergebnis:

id 	name 	last_use
2 	Kurt 	2010-07-01 21:42:54
3 	Karl 	2010-07-01 18:21:31
6 	Max 	2010-07-01 09:36:54
7 	Otto 	2010-07-01 21:40:27

Problematisch wird es jetzt, wenn zu einem "name" zwei (oder mehr) Datensätze mit identischem "last_use" existieren. (Davon muß man ggfs. immer ausgehen, wenn die Anderweitigkeit nicht durch einen entsprechenden Constraint sichergestellt ist).

[highlight=sql]
insert into doppeltest (name, last_use) values
('Max', '2010-07-01 09:36:54');
[/highlight]

Die o.g. Lösung liefert uns jetzt wieder doppelte Werte für "name".

id 	name 	last_use
2 	Kurt 	2010-07-01 21:42:54
3 	Karl 	2010-07-01 18:21:31
6 	Max 	2010-07-01 09:36:54
7 	Otto 	2010-07-01 21:40:27
8 	Max 	2010-07-01 09:36:54

Da zwischen den jetzt vorhanden Dopplungen nur anhand der "id" unterschieden werden kann, muß einfach entschieden werden, welche "id" in einem solchen Fall verwendet werden soll. Wir entscheiden uns für die Größte und damit genügt eine zusätzliche Gruppierung des Ergebnisses.

[highlight=sql]
select max(a.id) id, a.name, last_use
from doppeltest a
where a.last_use = (
select max(b.last_use)
from doppeltest b
where b.name = a.name
)
group by a.name, last_use
[/highlight]

Ergebnis:

id 	name 	last_use
3 	Karl 	2010-07-01 18:21:31
2 	Kurt 	2010-07-01 21:42:54
8 	Max 	2010-07-01 09:36:54
7 	Otto 	2010-07-01 21:40:27

Anmerkung: Durch die Verwendung der Aggregatfunktion MIN() kann man diesen Lösungsansatz einfach für die Frage nach dem Ältesten Datensatz anpassen.

Gruß Falk

[Falk Prüfer]

Neben der "klassischen" Variante gibt es auch noch eine Lösung, die gänzlich ohne Subselect auskommt. Dank einer ebenso cleveren wie trivialen Idee von frauwue kann ich die hier auch mit vorstellen.
[highlight=sql]
SELECT a.id, a.name, a.last_use
FROM doppeltest a
Left outer Join doppeltest b on b.name = a.name
and b.last_use > a.last_use
WHERE b.last_use is null
[/highlight]
Dieser Join macht sich den (eigentlich logischen) Umstand zunutze, dass es zu einem höchsten (bzw. niedrigstem) Datum kein noch höheres (bzw. noch niedrigeres) geben kann, und damit nur jeweils ein DS übrigbleibt, der die JOIN-Bedingung NICHT erfüllt.

Die Problematik bei zwei (oder mehr) Datensätze mit identischem "last_use" steht hierbei jedoch genauso wie bei der Variante mittels Subselect und lässt sich ebenso lösen.

Anmerkung:
Für dieses konkrete Beispiel spielt es zwar keine Rolle, da alle Felder als NOT NULL angelegt sind, dennoch möchte ich daraufhinweisen das die JOIN-Lösung "Schwächen" hat, wenn in "last_use" NULL-Werte enthalten sind. Diese werden mit selektiert, da sie die JOIN-Bedingung ebenfalls NICHT erfüllen.