MySQL: Was bewirkt eine Unterabfrage in der ORDER BY Klausel?

[ThomasLa]

Hallo,

das habe ich in einem älteren Post entdeckt:

  
SELECT id FROM db_articles  
ORDER BY fixedrating DESC, (  
  SELECT AVG(rating) FROM db_articles WHERE product_id=id  
) DESC  

Jetzt frage ich mich: Was bewirkt die Subquery (3. Zeile) überhaupt?? Ich hab da gerade eine Weile herumprobiert, aber nie eine Auswirkung der Unterabfrage beobachten können :-(

Ich hab sogar das komplette MySQL-Referenzhandbuch nach "order by" durchsucht, aber auch nix gefunden :-(

Es gibt einen MySQL Bug Report, wo ORDER BY (SELECT ...) vorkommt, aber dort wird auch nicht erklärt, was das Ergebnis sein sollte.

Weiß das vielleicht jemand von euch?

Danke!!

[ChrisB]

Hi,

SELECT id FROM db_articles
ORDER BY fixedrating DESC, (
  SELECT AVG(rating) FROM db_articles WHERE product_id=id
) DESC

>   
> Jetzt frage ich mich: Was bewirkt die Subquery (3. Zeile) überhaupt??  
  
Sie ermittelt mittels der Aggregatfunktion AVG() wohl die durchschnittliche Bewertung des Produktes.  
  

> Ich hab da gerade eine Weile herumprobiert, aber nie eine Auswirkung der Unterabfrage beobachten können :-(  
  
Zunaechst wird ja mal nach dem Feld fixedrating sortiert; erst wenn das bei zwei Produkten gleich ist, kommt der Durchschnitt des Feldes rating als zweites Sortierkriterium mit zum Zuge.  
  
MfG ChrisB  
  

-- 
„This is the author's opinion, not necessarily that of Starbucks.“

[ThomasLa]

Zunaechst wird ja mal nach dem Feld fixedrating sortiert; erst wenn das bei zwei Produkten gleich ist, kommt der Durchschnitt des Feldes rating als zweites Sortierkriterium mit zum Zuge.

Also entweder du hast das nicht zu Ende gedacht, oder ich versteh's nicht ;-)

Die Unterabfrage SELECT AVG(rating) wertet sich doch auf eine einzige Zahl aus, sagen wir mal 7. Somit steht dann insgesamt:
ORDER BY fixedrating, 7

Was soll das heißen??

Es gibt übrigens noch einen zweiten MySQL Bug Report, der aber auch nicht aufschlussreicher ist ;-)

[ChrisB]

Hi,

Zunaechst wird ja mal nach dem Feld fixedrating sortiert; erst wenn das bei zwei Produkten gleich ist, kommt der Durchschnitt des Feldes rating als zweites Sortierkriterium mit zum Zuge.

Also entweder du hast das nicht zu Ende gedacht, oder ich versteh's nicht ;-)

Dann denk' vielleicht mal bis zum Ende mit, vielleicht verstehst du's dann.

Die Unterabfrage SELECT AVG(rating) wertet sich doch auf eine einzige Zahl aus, sagen wir mal 7. Somit steht dann insgesamt:
ORDER BY fixedrating, 7

Was soll das heißen??

Da kommt keine fixe 7 raus, sondern fuer jede Produkt-ID ein individueller Wert.

MfG ChrisB

[ThomasLa]

Da kommt keine fixe 7 raus, sondern fuer jede Produkt-ID ein individueller Wert.

Das bezweifle ich stark, weil ja die Unterabfrage zuerst ausgeführt wird, oder?
Aber egal, selbst wenn du recht hast, steht halt insgesamt:
ORDER BY fixedrating, (7, 3.4, 9.2, 6, 5.9)

Was heißt das dann?? Woher weiß er, was er mit dieser Liste von Zahlen jetzt anfangen soll?

[ChrisB]

Hi,

Da kommt keine fixe 7 raus, sondern fuer jede Produkt-ID ein individueller Wert.

Das bezweifle ich stark, weil ja die Unterabfrage zuerst ausgeführt wird, oder?

Oder.

Aber egal, selbst wenn du recht hast, steht halt insgesamt:
ORDER BY fixedrating, (7, 3.4, 9.2, 6, 5.9)

Nein, natuerlich nicht.

Was heißt das dann?? Woher weiß er, was er mit dieser Liste von Zahlen jetzt anfangen soll?

"Er" hat keine Liste von Zahlen.
Die Unterabfrage liefert zu jeder Produkt-ID genau einen Datensatz mit einem Durchschnittswert der Bewertungen dieses einen Produktes.

MfG ChrisB

[ThomasLa]

Sakrament, das funktionert ja wirklich :-) - DANKE!!

Hier mal ein kleines Beispiel zur Illustration:

  
 CREATE TABLE `produkte` ( `id` tinyint(4) NOT NULL );  
 INSERT INTO `produkte` VALUES (1);  
 INSERT INTO `produkte` VALUES (2);  
 INSERT INTO `produkte` VALUES (3);  
  
 CREATE TABLE `bewertungen` ( `produkt` tinyint(4) NOT NULL,  
  `bewertung` tinyint(4) NOT NULL);  
 INSERT INTO `bewertungen` VALUES (1, 1);  
 INSERT INTO `bewertungen` VALUES (1, 2);  
 INSERT INTO `bewertungen` VALUES (2, 1);  
 INSERT INTO `bewertungen` VALUES (2, 2);  
 INSERT INTO `bewertungen` VALUES (2, 1);  

Dann erzeugt die Abfrage

  
 SELECT * FROM produkte ORDER BY (SELECT COUNT(*) FROM bewertungen WHERE produkte.id=bewertungen.produkt) DESC;  

das Resultat:
+----+
| id |
+----+
|  2 | <= Produkt Nr. 2 hat die meisten Bewertungen
|  1 |
|  3 |
+----+

So wie ich das jetzt verstehe, sind diese drei Bedingungen erforderlich:
*) Die Unterabfrage darf nur eine einzige Spalte enthalten. Sonst kommt die Fehlermeldung "Operand should contain 1 column(s)".
*) Die Unterabfrage darf nur eine einzige Zeile zurückliefern. Sonst kommt die Fehlermeldung "Subquery returns more than 1 row".
*) Die Unterabfrage muss eine WHERE-Klausel enthalten, über die eine Art temporäre Beziehung zwischen den korrespondierenden Feldern hergestellt wird. Sonst weiß "er" ja nicht, welchem Produkt er welchen COUNT-Wert zuordnen soll.

Stimmt das so?

Gibt's auch die Möglichkeit, dass ich die Sortierreihenfolge selber durch eine Art Liste (d.h. Unterabfrage) festlege? Also im obigen Beispiel sowas wie:

  
 SELECT * FROM bewertungen ORDER BY produkt (3,1,2)  

[Vinzenz Mai]

Hallo Thomas,

Gibt's auch die Möglichkeit, dass ich die Sortierreihenfolge selber durch eine Art Liste (d.h. Unterabfrage) festlege?

SELFHTML aktuell, Artikel, Datenbanken, Sortierfolgen in MySQL vorgeben.

Freundliche Grüße

Vinzenz

[ThomasLa]

Hallo Vinzenz,

danke, das ist wirklich GENIAL!! :-)

Aber müssen die Argumente von FIELD() tatsächlich immer vorgegebene Konstante sein (außer dem ersten)? Kann man da keine Unterabfrage verwenden? Ich hab's ca. so versucht (um die selbe Reihenfolge zu bekommen, wie in der Hilfstabelle):

  
SELECT FIELD(name, (SELECT name FROM hilfstabelle)) AS test, name FROM haupttabelle ORDER BY test;  

Da kommt aber immer die Fehlermeldung: "Subquery returns more than 1 row" :-(
Mit FIND_IN_SET() hab ich's auch probiert, selbes Resultat. Sind das tatsächlich beides sture String-Funktionen, die keinen "dynamischen" Input akzeptieren?

Gibt's da vielleicht eine andere Lösung? Oder muss ich tatsächlich zuerst die Hilfstabelle abfragen und mir aus dem Ergebnis in PHP eine Komma-getrennte Liste basteln, mit der ich dann die FIELD()-Funktion füttern kann?

Danke!!

[dedlfix]

echo $begrüßung;

Da kommt keine fixe 7 raus, sondern fuer jede Produkt-ID ein individueller Wert.
Das bezweifle ich stark, weil ja die Unterabfrage zuerst ausgeführt wird, oder?

Oder. Das soll eine korrelierte Unterabfrage darstellen, wenn es richtig geschrieben wäre. Als solche muss es sich ja auf einen bereits ermittelten Wert aus der Haupt-Abfrage beziehen und kann damit erst nach dessen Bekanntsein ausgeführt werden. Generell kann man sagen, dass ein Subquery zum gleichen Zeitpunkt ausgeführt wird wie die Klausel, in der es notiert steht. (Wenn der Optimierer das intern anders löst, kann das uns als Anwender egal sein. Wir sehen den Server ja nur als Blackbox.)

echo "$verabschiedung $name";

[ThomasLa]

Das soll eine korrelierte Unterabfrage darstellen

Danke, dedlfix! Wenn man weiß, wie die Dinge heißen, kann man viel leichter Infos dazu finden :-)
Werd mich morgen da mal schlau machen.

@Vinzenz:
Ja, das hab ich mir eh gedacht, dass es so gehört. Aber kapiert hab ich's trotzdem nicht ;-)

[Vinzenz Mai]

Hallo,

das habe ich in einem älteren Post entdeckt:

SELECT id FROM db_articles
ORDER BY fixedrating DESC, (
  SELECT AVG(rating) FROM db_articles WHERE product_id=id
) DESC

>   
> Jetzt frage ich mich: Was bewirkt die Subquery (3. Zeile) überhaupt?? Ich hab da gerade eine Weile herumprobiert, aber nie eine Auswirkung der Unterabfrage beobachten können :-(  
  
das Statement entspricht nicht Dennis' Beschreibung :-)  
Korrigiere es zu:  
  
~~~sql
  
SELECT id  
    FROM db_articles  
ORDER BY  
    fixedrating DESC, (  
        SELECT AVG(rating) FROM db_articles_rating WHERE product_id = id  
    ) DESC  

... und Dein Verständnisproblem sollte hoffentlich gelöst sein.

Freundliche Grüße

Vinzenz