GROUP BY in Kombination mit ORDER BY [MySQL]

versuch13

versuch13

Erfahrenes Mitglied
Ich habe zwei Tabellen

nodes
id | date | title | url | content

comments
id | nid | date | content

und möchte nun Abfragen, zu welchen Themen zuletzt Kommentare verfasst wurden.

Jetzt könnte man einfach nur die letzten Reihen der comments Tabelle abfragen,
aber das möchte ich nicht aus dem Grund falls z.B. mehrere Ergebnisreihen sich
auf eine Reihe der Tabelle nodes beziehen.

So, daher dachte ich mir, ich gruppiere die Ergebnismenge anhand des Feldes url
der Tabelle nodes da diese einmalig sind oder anhand des Feldes nid in comments.

Code: 
            SELECT
                one.`id`,
                one.`user`,
                two.`title`,
                two.`url`,
                CONCAT(SUBSTRING_INDEX(one.content, ' ', 3), ' …') as `content`
            FROM
                `comments` as one
            INNER JOIN
                `nodes` as two
            ON
                 one.`nid` = two.`id`
            GROUP BY
                two.`url`
            ORDER BY
                one.date DESC
            LIMIT 6

So bekomme ich auch zu jeder node nur ein Ergebnis, allerdings nicht in der korrekten Reihenfolge. ORDER BY liefert nicht das letzte Datum sondern das erste.

Verständlich was ich meine? Gibt es eine Lösung? Danke schon mal.
 
Hallo,

am einfachsten lässt sich die Abfrage über ein Subselect formulieren:

SQL: 
SELECT
	n.`title`,
	n.`url`,
	c.`id`,
	c.`user`,
	CONCAT(SUBSTRING_INDEX(c.`content`, ' ', 3), ' …') AS `content`
FROM
	`nodes` n
JOIN
	`comments` c ON	c.`id` = (
		SELECT c2.`id`
		FROM `comments` c2
		WHERE c2.`nid` = n.`id`
		ORDER BY c2.`date` DESC
		LIMIT 1
	)
ORDER BY
	c.`date` DESC
LIMIT 6

Grüße,
Matthias

PS: Deine Abfrage funktioniert deswegen nicht, weil erst gruppiert und dann sortiert wird. Außerdem führt es immer zu unvorhersehbaren Ergebnissen, wenn man bei einer gruppierenden Abfrage Spalten selektiert die nicht eine Aggregatsfunktion durchlaufen oder über die gruppiert wird (andere DBMS lassen dies z.B. überhaupt nicht zu).
 
Vielen Dank! Darauf hätte man eigentlich kommen können. Ich dachte nur es wäre evtl auch ohne Subselect möglich.

Zu deiner Aussage bezüglich GOUP BY und Aggregatsfunktionen noch eine Frage.
Das heißt GROUP BY sollte man nur auf Spalten anwenden die eine Aggregatsfunktion durchlaufen, oder GROUP BY sollte man nur verwenden wenn überhaupt eine Aggregatsfunktion zum Einsatz kommt?

Denn z.B.:

Code: 
                SELECT
                    one.`id`,
                    one.`date` as `datetime`,
                    DATE_FORMAT(one.`date`, '%H:%i') AS `time`,
                    DATE_FORMAT(one.`date`, '%d.%m.%y') AS `date`,
                    one.`url`,
                    one.`user`,
                    one.`title`,
                    COUNT(two.`nid`) as `anzahl`
                FROM
                    `nodes` as `one`
                LEFT JOIN
                    `comments` as `two`
                ON 
                    one.`id` = two.`nid`
                GROUP BY
                    one.`id`
                ORDER BY
                    `datetime` DESC
                LIMIT 6

Hier wende ich GROUP BY an, um COUNT mit in der Abfrage nutzen zu können, GROUP BY bezieht sich aber nicht auf die Spalte auf welche COUNT angewendet wird.
Funktionieren tut es, nur nach deiner Aussage frage ich mich jetzt ob es so nicht ganz korrekt ist?


Edit: Und wo ich das gerade in deinem Post sehe, wie kann ich hier SQL Code highlighten? Kann beim Eingabefeld nichts zu finden. Danke.
 
Zuletzt bearbeitet:
versuch13 hat gesagt.:
Zu deiner Aussage bezüglich GOUP BY und Aggregatsfunktionen noch eine Frage.
Das heißt GROUP BY sollte man nur auf Spalten anwenden die eine Aggregatsfunktion durchlaufen, oder GROUP BY sollte man nur verwenden wenn überhaupt eine Aggregatsfunktion zum Einsatz kommt?
Zum Vergrößern anklicken....
Ich muss zugeben, dass ich mich da nicht ganz deutlich ausgedrückt habe. Sorry dafür, aber ich war in Eile :)

Also nochmal ausführlicher: wenn man GROUP BY verwendet, dann sollte für alle selektierten Spalten gelten:
  • über die selektierte Spalte wird gruppiert oder
  • über die Spalte wird eine Aggregatsfunktion ausgeführt

In deinem Beispiel trifft das für die Spalten date, url, user und title nicht zu. In diesem Fall entstehen dadurch aber keine Probleme, da diese Spalten alle funktional von der gruppierten Spalte id abhängen (der Wert von id bestimmt die Werte aller dieser anderen Spalten eindeutig). Bei der Abfrage aus dem Ursprungsbeitrag ist das nicht so (zu einer Node kann es mehrere Kommentare geben, also bestimmt die URL der Node den Kommentar nicht funktional).

Grüße,
Matthias
 
Matthias Reitinger hat gesagt.:
Ich muss zugeben, dass ich mich da nicht ganz deutlich ausgedrückt habe. Sorry dafür, aber ich war in Eile :)

Also nochmal ausführlicher: wenn man GROUP BY verwendet, dann sollte für alle selektierten Spalten gelten:
  • über die selektierte Spalte wird gruppiert oder
  • über die Spalte wird eine Aggregatsfunktion ausgeführt
In deinem Beispiel trifft das für die Spalten date, url, user und title nicht zu. In diesem Fall entstehen dadurch aber keine Probleme, da diese Spalten alle funktional von der gruppierten Spalte id abhängen (der Wert von id bestimmt die Werte aller dieser anderen Spalten eindeutig). Bei der Abfrage aus dem Ursprungsbeitrag ist das nicht so (zu einer Node kann es mehrere Kommentare geben, also bestimmt die URL der Node den Kommentar nicht funktional).
Zum Vergrößern anklicken....
Ok, danke. In meinem tollen teurem Buch habe ich dazu nichts finden können.


Matthias Reitinger hat gesagt.:
Entweder mit
SQL: 
…
oder abkürzend
SQL: 
…
. Du hättest auch einfach meinen Beitrag zitieren können, dann bekommst du den „Quelltext“ ja zu Gesicht.
Zum Vergrößern anklicken....

Auf die Idee bin ich auch gekommen, aber man bekommt da was anderes zu Gesicht, siehe Anhang.
 

Anhänge

  • textfield.jpg
    textfield.jpg
    26,3 KB · Aufrufe: 349
versuch13 hat gesagt.:
Ok, danke. In meinem tollen teurem Buch habe ich dazu nichts finden können.
Zum Vergrößern anklicken....
Dann hast du wohl das falsches teure Buch gekauft ;)

versuch13 hat gesagt.:
Auf die Idee bin ich auch gekommen, aber man bekommt da was anderes zu Gesicht, siehe Anhang.
Zum Vergrößern anklicken....
Interessant. Du benutzt anscheinend den WYSIWYG-Editor, der kommt damit wohl nicht zu Recht. Zur Not kannst du das in den Einstellungen im Kontrollzentrum umstellen (ganz unten).

Grüße,
Matthias
 
Hallo,

ich sitze momentan am selben Problem. Ich habe es bisher auch mit einem Subselect gelöst, allerdings sind die Tabellen nun recht stark gewachsen, so dass der SQL-Befehl mit Subselect sehr langsam geworden ist.
Außerdem habe ich nicht immer MySQL-Server > 4.x zur Verfügung, so dass ich auch noch mit MySQL 3.x arbeiten muß - dort werden aber noch keine Subselects unterstützt.

Gibt es irgendeine Möglichkeit das Problem auch ohne Subselect zu lösen (in einem einzigen Statement) ?
Was mir fehlt wäre sowas wie: WHERE Timestamp=MAX(Timestamp) GROUP BY Tabelle.ID
Da Aggregat-Funktionen in der Where-Klausel nicht erlaubt sind, funktioniert dieses Beispiel natürlich nicht - aber vom Sinn her bräuchte ich genau so ein Konstrukt.


Direkt noch eine "related" Frage hinterher: die Subselects die ich bisher benutzt habe liefen immer über die WHERE-Klausel in der Art:
... WHERE t.Timestamp=(SELECT MAX(ts.Timestamp) FROM tabelle AS ts WHERE ts.TabellenID=t.TabellenID) GROUP BY ...

Die Möglichkeit einen Subselect über einen Join zu realisieren kannte ich noch nicht. Ich schätze mal, dass die Ergebnismenge mit beiden Methoden die gleiche sein wird - gibt es da irgendwelche Vor- bzw Nachteile der einen oder der anderen Methode (vor allem bzgl. der Geschwindigkeit) ?


Gruß
Allan Knox
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück