Zeilen von anderen Zeilen "subtrahieren"

[*Markus]

Hallo,

in einer versauten Tabelle ohne Schlüssel befinden sich Werte, die in etwa so aussehen:

mysql> select * from person;
+------+------------+------+------------+
| id   | name       | age  | gebdat     |
+------+------------+------+------------+
|    1 | Huber      |   37 | 1971-10-10 |
|    1 | Huber      |   37 | 1971-10-10 |
|    1 | Huber      |   37 | 1947-06-23 |
|    2 | Maier      |   37 | 1971-10-09 |
|    2 | Müller     |   30 | 1978-11-10 |
|    3 | Moser      |   44 | 1966-10-10 |
|    3 | Hofer      |   45 | 1966-10-10 |
|    5 | Hauser     |   30 | 1978-10-10 |
|    7 | Hauser     |   31 | 1977-10-10 |
|    7 | Hauser     |   31 | 1979-10-10 |
|    2 | Muster     |   20 | 1989-02-09 |
|    2 | Mustermann |   15 | 1994-12-01 |
|    6 | Musterfrau |   44 | 1966-10-10 |
|   10 | Burger     |   25 | 1983-01-20 |
|   10 | Burger     |   25 | 1983-04-09 |
|   10 | Burger     |   25 | 1983-03-08 |
+------+------------+------+------------+

Hieraus will ich sämtliche doppelte IDs entfernen und bei den IDs, wo unterschiedliche Datumsangaben sind, soll jeweils das höchste Datum überbleiben. Falls gleiche Datumsangaben pro ID vorhanden sind, soll irgend eine Zeile davon über bleiben. Hierzu habe ich folgendes Select-Statement angewendet mit folgendem Ergebnis:

mysql> select id, name, age, max(gebdat) from person group by id;
+------+------------+------+-------------+
| id   | name       | age  | max(gebdat) |
+------+------------+------+-------------+
|    1 | Huber      |   37 | 1971-10-10  |
|    2 | Maier      |   37 | 1994-12-01  |
|    3 | Moser      |   44 | 1966-10-10  |
|    5 | Hauser     |   30 | 1978-10-10  |
|    6 | Musterfrau |   44 | 1966-10-10  |
|    7 | Hauser     |   31 | 1979-10-10  |
|   10 | Burger     |   25 | 1983-04-09  |
+------+------------+------+-------------+
7 rows in set (0.00 sec)

Wie kann ich nun eine Anweisung formulieren, die mir alle Einträge löscht, außer jene, die ich hier als Ergebnis bekommen habe?
Ich dachte an ein Subselect, aber schaffe es nicht, dieses zu formulieren. Auch an Lösungen mit SELF JOINs habe ich gedacht, aber das bringt mir ebenfalls nichts.
Dann habe ich versucht, eine View zu erstellen, die mir diese Werte beinhaltet, aber wenn ich die Person-Tabelle kille, ist die View plötzlich auch leer. Ich hatte vor, die Person-Tabelle zu killen, und die View in die Tabelle neu einzuspielen. Scheint aber doch nicht so leicht zu sein, wie ich es mir dachte.

Markus

[Harlequin]

Yerf!

Ich hatte vor, die Person-Tabelle zu killen, und die View in die Tabelle neu einzuspielen. Scheint aber doch nicht so leicht zu sein, wie ich es mir dachte.

Ein View bezieht seine Daten immer von der Ursprungstabelle. Sobald die leer ist gibts auch nichts mehr im View. Füge dein Ergebnis des Selects in eine neue Tabelle ein, lösch die alte und benenn die neue entsrechend um.

Allerdings würde ich vorher mir das Statement und die Ergebnismenge nocheinmal genau anschauen, das ist nämlich eins dieser "kaputten" MySQL-GrouBy-Dinger...

Bist du sicher, das du immer den gewünschten Wert für "name" und "age" bekommst?

Gruß,

Harlequin

[Vinzenz Mai]

Hallo,

Hallo,

in einer versauten Tabelle ohne Schlüssel befinden sich Werte, die in etwa so aussehen:

mysql> select * from person;
+------+------------+------+------------+
| id   | name       | age  | gebdat     |
+------+------------+------+------------+
|    1 | Huber      |   37 | 1971-10-10 |
|    1 | Huber      |   37 | 1971-10-10 |
|    1 | Huber      |   37 | 1947-06-23 |
|    2 | Maier      |   37 | 1971-10-09 |
|    2 | Müller     |   30 | 1978-11-10 |
|    3 | Moser      |   44 | 1966-10-10 |
|    3 | Hofer      |   45 | 1966-10-10 |
|    5 | Hauser     |   30 | 1978-10-10 |
|    7 | Hauser     |   31 | 1977-10-10 |
|    7 | Hauser     |   31 | 1979-10-10 |
|    2 | Muster     |   20 | 1989-02-09 |
|    2 | Mustermann |   15 | 1994-12-01 |
|    6 | Musterfrau |   44 | 1966-10-10 |
|   10 | Burger     |   25 | 1983-01-20 |
|   10 | Burger     |   25 | 1983-04-09 |
|   10 | Burger     |   25 | 1983-03-08 |
+------+------------+------+------------+

mysql> select id, name, age, max(gebdat) from person group by id;

autsch.

+------+------------+------+-------------+
| id   | name       | age  | max(gebdat) |
+------+------------+------+-------------+
|    1 | Huber      |   37 | 1971-10-10  |
|    2 | Maier      |   37 | 1994-12-01  |
|    3 | Moser      |   44 | 1966-10-10  |
|    5 | Hauser     |   30 | 1978-10-10  |
|    6 | Musterfrau |   44 | 1966-10-10  |
|    7 | Hauser     |   31 | 1979-10-10  |
|   10 | Burger     |   25 | 1983-04-09  |
+------+------------+------+-------------+
7 rows in set (0.00 sec)

Wie kann ich nun eine Anweisung formulieren, die mir alle Einträge löscht, außer jene, die ich hier als Ergebnis bekommen habe?

Zweimal autsch. Du machst kaputte Daten noch kaputter.

Ja, bei der id 2 ist das Maximum beim Geburtstag der 1994-12-01. Dies führt jedoch zu einem Alter von 15 (wieso eigentlich?) und dies hat der gute Mustermann, aber nicht Maier.

Du kannst das von Dir gewünschte Ergebnis *nur* mit einer korrelierten Unterabfrage erhalten und nicht mit dem defekten MySQL-GROUP-BY-it'-not-a-bug-it's-a-feature-Verhalten. Siehe dazu meine Ausführungen in diesem Archivbeitrag und bestimmt 30 weiteren Beiträgen und genausovielen von Ilja.

Anschließend kannst Du so vorgehen, wie von Harlequin vorgeschlagen: SELECT in eine neue Tabelle, löschen der alten und Umbenennen der neuen Tabelle.

Du kommst doch von anderen Datenbankmanagementsystemen. Warum fällst Du auf das kaputte MySQL-Verhalten rein? Jedes andere DBMS lehnt Deine Abfrage als syntaktisch fehlerhaft zurück. Mir wäre es lieber, auch MySQL verhielte sich wie die anderen ...

Freundliche Grüße

Vinzenz

[*Markus]

Hallo,

Zweimal autsch. Du machst kaputte Daten noch kaputter.

Ja, offensichtlich hast du recht. Da ist mir wohl etwas entgangen. Zuerst hatte ich schon ein Subselect verwendet, aber durch das viele Herumprobieren kam ich dann irgendwie zu dieser "Lösung".

Anschließend kannst Du so vorgehen, wie von Harlequin vorgeschlagen: SELECT in eine neue Tabelle, löschen der alten und Umbenennen der neuen Tabelle.

Ok.

Du kommst doch von anderen Datenbankmanagementsystemen. Warum fällst Du auf das kaputte MySQL-Verhalten rein? Jedes andere DBMS lehnt Deine Abfrage als syntaktisch fehlerhaft zurück. Mir wäre es lieber, auch MySQL verhielte sich wie die anderen ...

Ehrlich gesagt dachte ich mir nichts dabei. Da ich mit MySQL bisher am wenigstens gemacht habe, dachte ich, dass es nicht schaden würde, mal etwas mehr damit zu tun.
Aber offensichtlich ist das wieder ein gutes Beispiel dafür, dass vieles in MySQL ja doch ein wenig anders zu sein scheint, als in anderen DBs.

Markus