MySQL auf sich selbst joinen?

[XMen]

Hallo!

Ich habe diese Abfrage probiert:

select max(tb1.id) as max_id from tb1 left join tb1 as tb2 on tb2.id = max_id where tb1.num = 1

Aber ich bekomme immer nur so einen Fehler, dass er max_id nicht kennt.

Warum kann ich das nicht so schreiben?

[Philipp Hasenfratz]

Halihallo XMen

select max(tb1.id) as max_id from tb1 left join tb1 as tb2 on tb2.id = max_id where tb1.num = 1

max_id ist zum Zeitpunkt des Joins noch nicht definiert. Was willst du tun?

Aber ich bekomme immer nur so einen Fehler, dass er max_id nicht kennt.

Korrekt.

Warum kann ich das nicht so schreiben?

Zum berechnen von max_id muss der Join bereits gebildet sein, folglich kann max_id
beim joinen noch gar nicht existieren.

Viele Grüsse

Philipp

[XMen]

Ich möchte aus einer Datenbank mehrere min und max ids auslesen.

Zu denen brauche ich aber noch andere Spalten, allerdings kann man die ja nicht mit einem mal auslesen.

SELECT text,max(id) gibt nicht den text bei max(id) aus, sondern irgendeinen.

Jetzt dachte ich halt, dass es hilft, wenn ich die Tabelle nochmal auf die min und max ids joine um so den text bei diesen ids zu bekommen.

[Philipp Hasenfratz]

Halihallo XMen

Zu denen brauche ich aber noch andere Spalten, allerdings kann man die ja nicht mit einem mal auslesen.

Doch, mit subselects, aber diese werden bei MySQL erst ab 4.1 unterstützt.

SELECT text,max(id) gibt nicht den text bei max(id) aus, sondern irgendeinen.

Sollte eigentlich gar keinen ausgeben sondern mit einer Fehlermeldung quittieren, aber
das ist ein "Bug" in MySQL.

Jetzt dachte ich halt, dass es hilft, wenn ich die Tabelle nochmal auf die min und max ids joine um so den text bei diesen ids zu bekommen.

SELECT b.text, max(a.id) AS max_id, b.id
   FROM   tb1 AS a,
          tb1 AS b
   WHERE
          a.id=b.id AND
          a.num=1
   GROUP BY
          b.id
   HAVING
          b.id=max_id

kommt mir hierzu als ungetesteter Lösungsvorschlag in den Sinn.

Nebenbemerkung: Du bräuchtest in deinem Vorschlag keinen LEFT JOIN, da die max(id) ja
in derselben Tabelle zwingend vorkommen muss.

Viele Grüsse

Philipp

[XMen]

Danke, das hat mich schon ein Stück weiter gebracht.

Allerdings habe ich jetzt noch folgendes Problem.

Ich habe noch eine 3. id (group_id), nach der ich gruppieren muss.

denn ich brauche nur jeweils von jeder group_id die höchste id und dazu eben den passenden text.

Ich habe deinen Vorschlag bereits getestet und er funktioniert, allerdings bekomme ich eben alle ids und nicht nur jeweils die höchste von jeder group_id.

[Philipp Hasenfratz]

Halihallo XMen

Ich habe deinen Vorschlag bereits getestet und er funktioniert, allerdings bekomme ich eben alle ids und nicht nur jeweils die höchste von jeder group_id.

Was hast du versucht? - Wie sieht der Query aus?

Viele Grüsse

Philipp

[XMen]

Halihallo XMen

Ich habe deinen Vorschlag bereits getestet und er funktioniert, allerdings bekomme ich eben alle ids und nicht nur jeweils die höchste von jeder group_id.

Was hast du versucht? - Wie sieht der Query aus?

Viele Grüsse

Philipp

»»

Den Query, den du mir geschrieben hast, habe ich genauso übernommen und es hat hingehauen.
Aber ich bekomme eben alle ids.

Also main_id ='1' passt.

Jetzt habe ich noch eine group_id, nach der ich eben gruppiere. group_id kommt öfters vor, id ist auto_increment.
Ich brauche eben von jeder group_id die höchste id mit zugehörigem text.

meine änderungen haben aber nichts gebracht :)

[Philipp Hasenfratz]

Halihallo XMen

Den Query, den du mir geschrieben hast, habe ich genauso übernommen und es hat hingehauen.
Aber ich bekomme eben alle ids.

Dann hat er aber nicht hingehauen, er soll nur eine Ergebnismenge anzeigen. Den Fehler
habe ich unten (wenn ich richtig vermute) korrigiert.

Also main_id ='1' passt.

was ist die main_id?

Jetzt habe ich noch eine group_id, nach der ich eben gruppiere. group_id kommt öfters vor, id ist auto_increment.

Ich fragte nicht nachdem, was du willst, sondern explizit nach dem Query, den du getestet
hast.

Ich brauche eben von jeder group_id die höchste id mit zugehörigem text.

was du brauchst weiss ich, ich wollte deinen Query sehen. Aber egal:

SELECT b.text, max(a.id) AS max_id, b.id, a.group_id
   FROM   tb1 AS a,
          tb1 AS b
   WHERE
          a.num=1
   GROUP BY
          b.id,a.group_id
   HAVING
          b.id=max_id
   LIMIT
          1,0

Ich hoffe ich habe keinen Denkfehler gemacht, ist schon etwas trickreicher ;)
Das LIMIT wird gebraucht, da wegen dem a.group_id dieselbe b.id mehrmals vorkommen wird.
Du würdest ohne einfach dieselbe Ziele mehrfach erhalten, deshalb kann man die letzteren
einfach wegschneiden.

Viele Grüsse

Philipp

[Philipp Hasenfratz]

Halihallo

SELECT b.text, max(a.id) AS max_id, b.id, a.group_id
   FROM   tb1 AS a,
          tb1 AS b
   WHERE
          a.num=1
   GROUP BY
          b.id,a.group_id
   HAVING
          b.id=max_id
   LIMIT
          1,0

Oh, doch ein Denkfehler. Du möchtst natürlich die Texte zu jeder Gruppe, nicht nur von
einer.

SELECT DISTINCT b.text, max(a.id) AS max_id, b.id, a.group_id
   FROM   tb1 AS a,
          tb1 AS b
   WHERE
          a.num=1
   GROUP BY
          b.id,a.group_id
   HAVING
          b.id=max_id

so... Man sollte mal einen Themenbereich "SQL-Akrobatik" erstellen ;-)

Viele Grüsse

Philipp

[XMen]

Danke. Ich habs mal getestet, leider tut sich aber nix. Der Browser beginnt zu laden, aber das wars schon :)

Ich teste immer per phpmyadmin

Ich teste genau die querys, die du hier schreibst.
Genau so wies da steht.

[XMen]

Kleine Änderung:

es tut sich doch was, genauso, wie ich es mir vorgestellt habe :)

Also soweit mal dankedanke:)

Aber: leider dauert die Abfrage ganze 15 Sekunden. Ich glaube ein kleiner Haken ist noch drin.

[Philipp Hasenfratz]

Halihallo XMen

Aber: leider dauert die Abfrage ganze 15 Sekunden. Ich glaube ein kleiner Haken ist noch drin.

Index auf num, Unique Index auf id (implizit durch Primary Key), Index auf group_id.
Dass dürfte dir nochmals einen Performanceschub verpassen.

Ansonsten: Natürlich ist die Abfrage sau ineffizient, aber es ist mit MySQL 3.x nunmal
nicht anders Möglich, als über derart kurriose SQL-Abfragen zum gewünschten Ergebnis
zu kommen (mit Subselects ginge dies tausendmal performanter, werden aber erst seit 4.1
unterstützt), zumindest fällt mir im Moment keine bessere ein.

Viele Grüsse

Philipp

[XMen]

Bringt leider auch nix, ist plötzlich sogar bei 25-27 Sekunden :)

Mein Hauptproblem ist folgendes. Ich könnte auch mehrere Queries machen, das wäre wahrscheinlich effizienter.

Aber ich brauche das gleiche, was du mir jetzt mit MAX() gemacht hast auch mit MIN(). Dabei muss ich aber immer nach max(id) absteigend ordnen, nur wie mache ich das, wenn ich die min(id) selektiere?

Ein QUery habe ich erstellt, bei dem werden aber, wie schon gesagt, als text immer die Werte bei min(id) genommen. Wenn ich sicher sein könnte, dass das immer so ist, wäre das kein Problem. Dann hätte ich ja schon alle Angaben zu min(id), die max(id) und bräuchte dann nur noch ein 2. Query wo ich die Details der Max(id)s abfrage.

[Philipp Hasenfratz]

Halihallo XMen

Bringt leider auch nix, ist plötzlich sogar bei 25-27 Sekunden :)

Puh...

Mein Hauptproblem ist folgendes. Ich könnte auch mehrere Queries machen, das wäre wahrscheinlich effizienter.

Ja. Gut möglich.

Aber ich brauche das gleiche, was du mir jetzt mit MAX() gemacht hast auch mit MIN(). Dabei muss ich aber immer nach max(id) absteigend ordnen, nur wie mache ich das, wenn ich die min(id) selektiere?

Ich verstehe nicht was du meinst.

Ein QUery habe ich erstellt, bei dem werden aber, wie schon gesagt, als text immer die Werte bei min(id) genommen. Wenn ich sicher sein könnte, dass das immer so ist, wäre das kein Problem. Dann hätte ich ja schon alle Angaben zu min(id), die max(id) und bräuchte dann nur noch ein 2. Query wo ich die Details der Max(id)s abfrage.

Das verstehe ich auch nicht. Kannst du nochmals sagen, was du wie und warum machen
willst?

Viele Grüsse

Philipp

[XMen]

Also mal ein Beispiel:

SELECT text, MAX(id) AS max_id FROM table WHERE num='1' GROUP BY group_id ORDER BY max_id DESC

bringt mir zwar alles in der richtigen Reihenfolge, aber ich bekomme als text den Wert des Feldes 'text' bei MIN(ID) und nicht den direkt bei MAX(ID).
Wenn das jetzt allgemein zutreffen würde wäre es ja ok.
Dann bräuchte ich nur noch 'text' zu MAX(ID), das würde ich dann einfach so machen, dass ich alle MAX(ID) aus dem ersten Query auslese und dann mit zig OR (WHERE ID='3' OR ID='7' ...) in einer zweiten abfrage alle 'text' bei MAX(ID) hole.

Aber leider holt sich MySQL den 'text' in der ersten Abfrage ja scheinbar zufällig.

Da liegt das Hauptproblem.

[Philipp Hasenfratz]

Halihallo XMen

So, ich habe mich über meine Dummheiten in diesem Thread so aufgeregt, dass ich erstmal
etwas Beach-Volleyball spielen musste :-)
Irgendwann hat's mich doch ziemlich stark auf meine "Birne" geknallt und da hatte ich
dann folgende Idee: ;)

SELECT a.text, a.group_id, MAX(b.id) AS max_id
   FROM
      tb1 AS a,
      tb2 AS b
   WHERE
      a.group_id=b.group_id AND
      a.num=1
   GROUP BY
      a.id,b.group_id
   HAVING
      a.id=max_id

So, dies sollte jetzt wieder viel performanter sein. Ich hatte mir vorher wenig dabei
gedacht und das Grundprinzip "Möglichst schnell die Datenmenge verkleinern" völlig
ausser acht gelassen. Durch die WHERE-Condition dass a.group_id=b.group_id reduziert
sich die Datenmenge um ein vielfaches und die Performance steigt, zudem kommt der Index
wesentlich besser zum tragen. Dies ist zwar immer noch nicht sehr performant, aber
sicher besser als 15 Sekunden.

Soviel zu dem, falls dies dann immer noch nicht performant sein sollte, was ich aber
nicht glaube, folgendes:

SELECT MAX(id)
   FROM
      tb1
   WHERE
      num=1
   GROUP BY group_id

mit den MAX(id)'s machst du folgendes:

SELECT text
   FROM
      tb1
   WHERE
      id IN ([Komma-Liste-von-MAX(id)'s])
   ORDER BY
      id DESC

sollte ziemlich performant sein.

dein Vorschlag: "SELECT text, MAX(id) AS max_id FROM table WHERE num='1' GROUP BY
group_id ORDER BY max_id DESC"

Nun ja, ORDER BY max_id ist unglücklich, den würde ich im zweiten Query schreiben, da
dann der Unique Index des Primary Keys (id) verwendet werden kann => wesentlich
schneller. Bei deinem Query müsste zuerst gruppiert werden und auf der temporären
Zwischenergebnistabelle sortiert werden (dauert länger). Zudem hat hier "text" wirklich
_nichts_ zu suchen! - Wie gesagt, das ist ein "Bug" in MySQL. text darf nur ohne
Agregatsfunktionen (SUM/AVG/MAX/MIN/COUNT) vorkommen, wenn du nach dem Primary Key 'id'
auf dieselbe Tabelle gruppierst. Du hast richtig festgestellt, dass MySQL hier einen
zufälligen Text auswählt.

Und wenn ich jetzt nochmals einen Denkfehler habe, geh ich schlafen, Himmel noch eins!
;-)

Viele Grüsse

Philipp