zusätzliche Daten bei GROUP BY abfragen

[Andreas Korthaus]

Hallo!

Ich komme immer wieder in die Situation, dass ich per "GROUP BY" z.B. einen maximalen Wert in einer Spalte auslese, dann aber auch gerne gleichzeitig den zugehörigen Schlüssel (ID) auslesen würde, was leider nicht geht wenn die ID nicht hinter GROUP BY aufgelistet ist.

Mal als Beispiel eine Produkt-Tabelle:

id | warengruppe | preis...
---+-------------+---------
1  | A           | 123
2  | A           | 234
3  | B           | 345

Jeder Datensatz hat eine eigene "id". Wie komme ich jetzt also an die ID des jeweilig teuersten Produktes in einer Warengruppe?

SELECT MAX(preis)  
  FROM produkte  
  GROUP BY warengruppe

Aber wie komme ich jetzt an die ID? Man könnte jetzt herkommen und jede Warengruppe einzelnd mit ORDER BY preis LIMIT 1 abfragen, z.B.:

SELECT id  
  FROM produkte  
  WHERE warengruppe = 'A'  
  ORDER BY preis DESC  
  LIMIT 1

aber wenn das jetzt 1000 Warengruppen sind? Oder der SQL-Code deutlich komplexer? Gibt es keine elegantere Möglichkeit?

Grüße
Andreas

[Tom]

Hello,

SELECT ID, MAX(preis)
    FROM produkte
    GROUP BY warengruppe

Jetzt hast Du mich aber verwirrt. ;-)

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Klaus Mock]

Hallo,

SELECT ID, MAX(preis)
    FROM produkte
    GROUP BY warengruppe

Das ist an sich kein gültiges SQL-Statement, da es nicht eindeutig ist. Das funktioniert nur in kaputten Datenbanken.

Was passiert wenn der Datenbestand genau so aussieht:

ID     PREIS
1      100
2      100
3      200
4      200

Eine mögliche Lösungsvariante ist:

SELECT id, preis
  FROM tabelle
WHERE preis = (SELECT MAX(preis) FROM tabelle)

Allerdings muss dazu das Datenbanksystem auch Sub-Queries verstehen.

Grüße
  Klaus

[Andreas Korthaus]

Hallo!

SELECT ID, MAX(preis)
    FROM produkte
    GROUP BY warengruppe

Das ist an sich kein gültiges SQL-Statement, da es nicht eindeutig ist. Das funktioniert nur in kaputten Datenbanken.

jepp, siehe: http://aktuell.de.selfhtml.org/tippstricks/datenbanken/having/

Eine mögliche Lösungsvariante ist:

SELECT id, preis
  FROM tabelle
WHERE preis = (SELECT MAX(preis) FROM tabelle)

Allerdings muss dazu das Datenbanksystem auch Sub-Queries verstehen.

Hm, bisher habe ich immer einen Bogen um etwas fortschrittlichere Features gemacht, da ich nach Möglichkeit keinen DB-spezifischen Code schreiben will. Ich verwende eine DB-Abstraktion, und achte darauf immer einfaches/portables SQL zu verwenden - und mir ist klar dass ich hierdurch ineffizienteren Code schreibe. Kennst Du oder jemand anders hier eine nette Gegenüberstellung was welches RDBMS unterstützt? Bei mir sollten zumindest folgende unterstützt werden:

PostgreSQL
MySQL
Oracle
IBM DB2
MSSQL

das wären die wichtigsten. Z.B. bei Sub-Queries, das ist ein Feature was ich schon des öfteren gut hätte gebrauchen können. MySQL kann das ja auch ab 4.1, allerdings habe ich - je komplexer die Queries werden - immer größere Befürchtungen dass es nicht mehr überall läuft. Habt Ihr da Erfahrungen?

Grüße
Andreas

[Daniela Koller]

Hi Andreas

Kennst Du oder jemand anders hier eine nette Gegenüberstellung was welches RDBMS unterstützt? Bei mir sollten zumindest folgende unterstützt werden:

PostgreSQL
MySQL
Oracle
IBM DB2
MSSQL

Postgres und DB2 (ich kenn nur die Mainframe-Version) können Sub-Queries absolut problemlos, ebenso Oracle.  Oracle ist in sehr alten Versionen (afair <=7) etwas schwach was Joins angeht. Da gab es eine eigene Syntax. MySQL verursacht sowieso nur Knoten in den Fingern und sollte ignoriert werden bis die wenigstens den Minimal-Level von SQL92 leidlich implementieren. MSSQL hab ich bisher nie mit zu tun gehabt.

Eine nette Gegenüberstellung (wenn auch nicht in Tabellenform) ist in SQL in a Nutshell von O'Reilly drin.

Gruss Daniela

[Andreas Korthaus]

Hallo Daniela!

Postgres und DB2 (ich kenn nur die Mainframe-Version) können Sub-Queries absolut problemlos, ebenso Oracle.  Oracle ist in sehr alten Versionen (afair <=7) etwas schwach was Joins angeht.

Aber so einfach LEFT JOINS der Art

SELECT p.id as id, p.preis as preis, w.name as name  
  FROM produkte as p  
  LEFT JOIN warengruppen as w  
  ON p.wgruppe = w.wgruppe  
  WHERE p.preis > 100  
  ORDER BY p.preis DESC

müssten die aktuelleren Versionen doch alle können, oder entdeckst Du darin auch einen fiesen MySQL-Dialekt? ;-)

Mal noch ne kleine Stufe komplizierter:

SELECT (SUM(p.preis * p.menge) * w.rabatt) as endpreis, w.wgruppe as warengruppe  
  FROM produkte as p  
  LEFT JOIN warengruppen as w  
  ON p.wgruppe = w.wgruppe  
  WHERE p.preis > 100  
  GROUP BY w.wgruppe, w.rabatt  
  ORDER BY p.preis DESC

Ist das immer noch "Standard genug"? Vielleicht fällt Dir ja spontan eine "Todsünde" auf ;-)

SUM() unterstützen AFAIK alle von mir genannten RDBMS. Allerdings bin ich mir nicht so sicher wie das ist bei GROUP BY und der Multiplikation mit aggregierten Werten, aber eigentlich sollte das doch funktionieren, bei MySQL z.B. gehts - was ja leider nicht viel heißt :-(

Da gab es eine eigene Syntax.

Wie für so vieles, z.B. Limitierungen...

MySQL verursacht sowieso nur Knoten in den Fingern und sollte ignoriert werden bis die wenigstens den Minimal-Level von SQL92 leidlich implementieren.

Naja, kommt immer drauf an womit man gelernt hat. Wenn man Features die woanders Standard sind nicht kennt, vermisst man sie am Anfang natürlich nicht. Ist jedenfalls alles andere als einfach portablen SQL-Code zu schreiben, vor allem wenn es dann etwas komplexer wird...

Eine nette Gegenüberstellung (wenn auch nicht in Tabellenform) ist in SQL in a Nutshell von O'Reilly drin.

Danke für den Tipp, werd ich mal demnächst nen Blick reinwerfen wenn ich in der Buchhandlung bin!

Viele Grüße und Danke für die Hinweise
Andreas

[Daniela Koller]

Hi Andreas

Aber so einfach LEFT JOINS der Art

SELECT p.id as id, p.preis as preis, w.name as name

FROM produkte as p
  LEFT JOIN warengruppen as w
  ON p.wgruppe = w.wgruppe
  WHERE p.preis > 100
  ORDER BY p.preis DESC

>   
> müssten die aktuelleren Versionen doch alle können, oder entdeckst Du darin auch einen fiesen MySQL-Dialekt? ;-)  
  
Evtl ist ein OUTER zwischen LEFT und JOIN nötig, sonst wie gesagt, afair ab Oracle 8 gehts.  
  

> Mal noch ne kleine Stufe komplizierter:  
>   
> ~~~sql

SELECT (SUM(p.preis * p.menge) * w.rabatt) as endpreis, w.wgruppe as warengruppe  

>   FROM produkte as p  
>   LEFT JOIN warengruppen as w  
>   ON p.wgruppe = w.wgruppe  
>   WHERE p.preis > 100  
>   GROUP BY w.wgruppe, w.rabatt  
>   ORDER BY p.preis DESC

Ist das immer noch "Standard genug"? Vielleicht fällt Dir ja spontan eine "Todsünde" auf ;-)

Das könnte evtl Ärger geben da der Preis nicht im Resultat mit drin ist. Manche DBMS können nur nach Feldern sortieren, die auch ausgegeben werden.

Gruss Daniela

[Christian Seiler]

Hallo Daniela,

Evtl ist ein OUTER zwischen LEFT und JOIN nötig, sonst wie gesagt, afair ab Oracle 8 gehts.

Nein, erst ab 9. Ich habe die leidvolle Erfahrung gemacht, dass Oracle bis einschließlich Version 8 nur so eine Syntax:

WHERE feld1 = feld2 (+)

unterstützt.

Viele Grüße,
Christian

[Ilja]

yo,

Nein, erst ab 9. Ich habe die leidvolle Erfahrung gemacht, dass Oracle bis einschließlich Version 8 nur so eine Syntax:

WHERE feld1 = feld2 (+)

unterstützt.

wobei sich das leid doch im rahmen halten sollte. schließlich ist es das gleiche nur in einer anderen oracle spezifischen schreibweise ;-)

Ilja

[Christian Seiler]

Hallo Ilja,

wobei sich das leid doch im rahmen halten sollte. schließlich ist es das gleiche nur in einer anderen oracle spezifischen schreibweise ;-)

Wenn man portablen Code schreiben will, hält es sich halt nicht wirklich im Rahmen. Aber egal, Oracle ab 9 kann's ja und inzwischen gibt's Version 10.

Viele Grüße,
Christian

[Ilja]

yo,

Wenn man portablen Code schreiben will, hält es sich halt nicht wirklich im Rahmen.

ich denke mal, portablen code ist sehr schwer zu entwickeln. nicht nur wegen der syntaktischen unterschiede, sondern auch weil unterschiedliche dbms die gleiche syntax auch unterschiedlich ausführen würden. bei oracle zum beispiel kann es eine erhebliche rolle spielen, das die tabellennamen in einer bestimmten reihenfolge kommen. an anderes dbms kann aber wiederum eine andere ausführung produzieren, obwohl es zu keiner syntax-fehlermeldung kommt.

Ilja

[Klaus Mock]

Hallo,

Hm, bisher habe ich immer einen Bogen um etwas fortschrittlichere Features gemacht, da ich nach Möglichkeit keinen DB-spezifischen Code schreiben will.

z.B. das von Tom vorgeschlagene Statement ist eindeutig DB-spezifisch, da es afaik (zum Glück) von keinem anderen DB-System ausser mySQL akzeptiert wird.

Andererseits war ich jetzt ziemlich überrascht, als ich einmal JOIN bei einer Oracle 9 Datenbank ausprobiert habe, und es tatsächlich funktionierte. Und ich arbeite jetzt schon eine Zeitlang mit Oracle datenbanken und hier sind auch noch einige  eingefleischte Oracle-Programmierer, die anscheinend aber alle JOIN nicht verwenden.

Grüße
  Klaus

[Andreas Korthaus]

Hi Klaus!

z.B. das von Tom vorgeschlagene Statement ist eindeutig DB-spezifisch, da es afaik (zum Glück) von keinem anderen DB-System ausser mySQL akzeptiert wird.

Ja, ich habe halt ganz konkret das Problem wo ich _weiß_ dass es selbst bei MySQL nicht funktionieren kann. Manchmal kann ich es umgehen wenn ich z.B. eine ID mit MAX(ID) ermuttle, weil ich hier aufgrund von verwendeten Sequenzen sichergehen kann, dass es die höchste ID ist die ich haben will. Allerdings schafft das eine fiese Abhängigkeit, die früher oder später evtl. mal sehr unangenehme Folgen haben kann. Daher wäre mir eine andere Lösung schon lieber. Allerdings funktioniert Deine ja auch erst seit MySQL 4.1.

Andererseits war ich jetzt ziemlich überrascht, als ich einmal JOIN bei einer Oracle 9 Datenbank ausprobiert habe, und es tatsächlich funktionierte. Und ich arbeite jetzt schon eine Zeitlang mit Oracle datenbanken und hier sind auch noch einige  eingefleischte Oracle-Programmierer, die anscheinend aber alle JOIN nicht verwenden.

Ja, mit Oracle & Co. wird oft ganz anderes SQL geschrieben als mit MySQL. Ich habe bisher nur Teile meines SQL-Codes in DB2 und PostgreSQL getestet, und musste hierfür schon einiges umstellen/vereinachen. Ich glaube Oracle ist da noch spezieller. Ich verwende absichtlich kein Funktionen (wie Datum...) außer den wichtigsten Aggregatfunktionen.

Grüße
Andreas

PS: weiß jemand wie das mit Berechnung in SQL ist, also mit welcher Genauigkeit die jeweils vorgenommen werden? Bei MySQL AFAIK mit DOUBLE Genauigkeit.

[Ilja]

yo,

Ich komme immer wieder in die Situation, dass ich per "GROUP BY" z.B. einen maximalen Wert in einer Spalte auslese, dann aber auch gerne gleichzeitig den zugehörigen Schlüssel (ID) auslesen würde, was leider nicht geht wenn die ID nicht hinter GROUP BY aufgelistet ist.

GROUP BY hat erst mal nichts mit einem maximalen wert auslesen zu tun, sondern unterteilt eine abfrage in mehrere gruppen. du meinst sicherlich aus der gruppe den jeweils höchsten wert und die zugehörige id auslesen ;-)

da gibt es schon wege, dies zu erreichen, auch ohne den weg von mysql über russisch roulette zu gehen. du musst den kern von aussen angehen, sprich erstmal die ausgabespalten nehmen. dann gehst du ins innere der unterabfrage.

SELECT tab1.id, tab1.preis, tab1.warengruppe
FROM tabelle as tab1
WHERE tab1.preis =
   (SELECT MAX(tab2.preis) FROM tabelle as tab2 WHERE tab2.warengruppe=tab1.warengruppe)

Ilja

[Andreas Korthaus]

Hallo!

GROUP BY hat erst mal nichts mit einem maximalen wert auslesen zu tun, sondern unterteilt eine abfrage in mehrere gruppen. du meinst sicherlich aus der gruppe den jeweils höchsten wert und die zugehörige id auslesen ;-)

Ja ;-)

da gibt es schon wege, dies zu erreichen, auch ohne den weg von mysql über russisch roulette zu gehen. du musst den kern von aussen angehen, sprich erstmal die ausgabespalten nehmen. dann gehst du ins innere der unterabfrage.

SELECT tab1.id, tab1.preis, tab1.warengruppe
FROM tabelle as tab1
WHERE tab1.preis =
   (SELECT MAX(tab2.preis) FROM tabelle as tab2 WHERE tab2.warengruppe=tab1.warengruppe)

Ja, das ist wohl das beste. Hast Du Erfahrungswerte in welchen RDBMS das so wie Du es hier aufschreibst reibungslos funktioniert?

Grüße
Andreas

[Ilja]

yo,

Ja, das ist wohl das beste. Hast Du Erfahrungswerte in welchen RDBMS das so wie Du es hier aufschreibst reibungslos funktioniert?

es sollten alle rdbms machen, die korrelierende unterabfragen können, zum beispiel oracle oder mysql in der aktuellen version. die abfrage ist aus dem kopf geschrieben, sprich hier und da kann immer ein kleiner fehler sein. aber von der sache her sollte sie stimmen, also einfach mal ausprobieren und sagen, wenn und wo es klemmt.

Ilja