MySQL - Rangfolge mit Mehrfachplatzierungen

[mike]

Hallo,
ich habe folgendes Problem:

Vorhanden: Tabelle:

teilnehmer, punkte
A, 15
B, 15
C, 10
D, 8
...

und ich will jetzt >einen Starter< mit seiner Platzierung ausgeben, also z.B.:

platzierung, teilnehmer, punkte
1, A, 15   für [X] = 0
oder
1, B, 15   für [X] = 1
oder
2, C, 10   für [X] = 2
oder
3, D, 8   für [X] = 3

Bisher mache ich nur Folgendes:
SELECT * FROM tabelle ORDER BY punkte LIMIT [X], 1;

Die Platzierung habe ich aber noch nicht hinbekommen...

Danke im Vorraus,
mike

[ChrisB]

Hi,

teilnehmer, punkte
A, 15
B, 15
C, 10
D, 8
...

und ich will jetzt >einen Starter< mit seiner Platzierung ausgeben, also z.B.:

platzierung, teilnehmer, punkte
1, A, 15   für [X] = 0
oder
1, B, 15   für [X] = 1
oder
2, C, 10   für [X] = 2
oder
3, D, 8   für [X] = 3

Bisher mache ich nur Folgendes:
SELECT * FROM tabelle ORDER BY punkte LIMIT [X], 1;

Zum einen musst du hier nach punkte DESC (also in absteigender Reihenfolge) sortieren, wenn du obiges Ergebnis erreichen willst; und zum anderen *muss* das Feld teilnehmer auch noch mit in die Sortiervorschrift aufgenommen werden, denn sonst wäre es dem Zufall überlassen, wie zwei Teilnehmer mit gleicher Punktzahl sortiert würden, und damit wäre eine "PLatzvergabe" so wie du sie skizziert hast, nicht möglich.

Wir haben also eine Ergebnismenge, die mit ORDER BY punkte DESC, teilnehmer zu sortieren ist.

Die Platzierung habe ich aber noch nicht hinbekommen...

Die "Platzierung" eines Datensatzes in einer sortierten Menge lässt sich ermitteln, in dem man die Datensätze zählt, die kleiner bzw. grösser (je nach Sortierung) sind, bezogen auf den Vergleich der gewählten Sortierkriterien.
Diese Zählung lässt sich, entsprechende MySQL-Version vorausgesetzt, in einem Subquery unterbringen:

SELECT  
  (  
    SELECT COUNT(*)                            // selektiere die Anzahl der Datensätze  
    FROM tabelle t2                            // aus der Tabelle tabelle, der wir hier einen Aliasnamen t2 geben  
    WHERE t2.punkte > t1.punkte                // deren Punktestand entweder grösser ist,  
          OR (  
            t2.punkte = t1.punkte              // oder gleich gross  
            AND t2.teilnehmer < t1.teilnehmer  // und mit "kleinerem" Teilnehmernamen  
          )                                    // als die jeweiligen Werte aus der "äusseren" Tabelle t1  
  
    ORDER BY t2.punkte DESC, t2.teilnehmer     // sortiert nach Punkten absteigend und Teilnehmer  
  
  ) + 1                                        // und addiere 1 auf diese Anzahl drauf, denn für den ersten Datensatz  
                                               // ist die Anzahl der "davor" liegenden Datensätze ja logischerweise 0  
  
  AS platzierung,                              // gebe diesem neu ermittelten Wert den Aliasnamen platzierung  
  teilnehmer,  
  punkte  
  FROM tabelle t1                              // Aliasname für die "äussere" Tabelle  
  ORDER BY punkte DESC, teilnehmer 

Damit bekommen wir bei obiger Beispieltabelle schon mal folgendes heruas:

platzierung  teilnehmer  punkte
1            A           15
2            B           15
3            C           10
4            D           8

Und an das Ende der gezeigten Query kannst du jetzt noch deine LIMIT-Klausel dranhängen, um damit einen einzelnen Teilnehmer, der die Platzierung X haben soll, zu ermitteln.

MfG ChrisB

[ChrisB]

Hi,

jetzt hab' ich doch glatt die "falschen" Kommentarzeichen genommen, // aus PHP statt dem # für SQL - Kommentare also rauslöschen bzw. Kommentarzeichen ersetzen, wenn du das testen willst, sonst gibt's eine Fehlermeldung.

Und an das Ende der gezeigten Query kannst du jetzt noch deine LIMIT-Klausel dranhängen, um damit einen einzelnen Teilnehmer, der die Platzierung X haben soll, zu ermitteln.

Da wir ja 1 draufaddiert haben, um eine bei 1 beginnende Platzierung zu erhalten, gilt hier natürlich X für Platzierung X+1, also LIMIT 0, 1 für ersten Platz, etc.

Und das abschliessende

ORDER BY punkte DESC, teilnehmer

könnte man natürlich auch noch durch
ORDER BY platzierung [ASC]
ersetzen; weiss aber nicht, ob das hinsichtlich der Performance noch viel Unterschied macht.

MfG ChrisB

[mike]

Ich danke euch für die ausführlichen Antworten...

@Ilja: Ja, das mit der gleichen Platzierung war für mich einer der größten Stolpersteine...

Zweiter Stolperstein: Ich habe immer in die Richtung self-join für die Paltzierung gedacht, geht aber dann doch mit Subquery ;-)

Danke nochmal,
mike

[mike]

Hallo nochmal,
ich hätte jetzt doch noch mal ne kleine zusätzliche Frage ;-]

Angenommen, die "Punkte" kommen nicht aus einer Tabelle, sondern werden aus zwei verschiedenen (Begegnungen und Teilnehmer) gezählt:

SELECT t.id AS teilnehmer,  
COUNT(b.gewinner) AS punkte  
FROM teilnehmer t LEFT JOIN begegnungen b ON t.id = b.gewinner  
GROUP BY t.id ORDER BY punkte DESC, s.id;

Dann sollte ich obiges Ergebnis doch genauso erhalten, wenn ich

SELECT t.id AS teilnehmer,  
COUNT(b.gewinner) AS punkte,  
(SELECT COUNT(s.id)  
        FROM teilnehmer t2 LEFT JOIN begegnungen b2 ON t2.id = b2.gewinner  
        WHERE COUNT(b2.gewinner) > punkte  
       ) + 1 AS platzierung  
FROM teilnehmer t LEFT JOIN begegnungen b ON t.id = b.gewinner  
GROUP BY t.id ORDER BY punkte DESC, s.id;

ausführe, oder habe ich einen Denkfehler?
Jedenfalls zeigt mir MySQL einen Fehler bei "WHERE COUNT(b2.gewinner) > punkte"
Oder bräuchte ich an der Stelle etwa noch eine zweite Subquery?

Wäre super, wenn mir nochmal jemand irgendwie auf die Sprünge helfen könnte, muss ja auch nicht so ausführlich sein, wie die oben ;-)

Gruß, mike

[Ilja]

yo,

ausführe, oder habe ich einen Denkfehler?

du hast einen denkfehler, HAVING ist dein freund.

SELECT tab.id Teilnehmer, tab.Punkte,
      (SELECT COUNT(s.id)
        FROM teilnehmer t2
        LEFT JOIN begegnungen b2 ON t2.id = b2.gewinner
        HAVING COUNT(b2.gewinner) > tab.punkte
       ) + 1 AS platzierung
FROM (SELECT t.id, COUNT(b.gewinner) Punkte,
      FROM teilnehmer t
      LEFT JOIN begegnungen b ON t.id = b.gewinner
      GROUP BY t.id
     ) tab
ORDER BY 2 DESC, 1
;

Ilja

[mike]

Vielen Dank, dass man Abfragen auch auf Abfragen durchführen kann, wusste ich gar nicht...cool

Nur bei der Platzierung stimmt noch was nicht ganz,

hier

(SELECT COUNT(t2.id)
        FROM teilnehmer t2
        LEFT JOIN begegnungen b2 ON t2.id = b2.gewinner
        HAVING COUNT(b2.gewinner) > tab.punkte
       ) + 1 AS platzierung

zählt er mir einfach alle Begegnungen ab, und erhöht diese Zahl um eins.

Das Problem ist, das COUNT(t2.id) sich auf die Begegnungen und nicht auf die Punkte bezieht, denke ich - oder so...

Trotzdem bin ich schon nen Megaschritt weiter als vorher, danke.

[mike]

Habe es jetzt selbst rausgekriegt, geht so:

  
SELECT tab.id Teilnehmer, tab.Punkte  
      (SELECT COUNT(tab2.id)  
       FROM (SELECT t.id, COUNT(b.gewinner) AS Punkte,  
             FROM teilnehmer t  
             LEFT JOIN begegnungen b ON t.id = b.gewinner  
             GROUP BY t.id  
             ) tab2  
       WHERE tab2.Punkte > tab.Punkte  
       ) + 1 AS platzierung  
FROM (SELECT t.id, COUNT(b.gewinner) AS Punkte  
      FROM teilnehmer t  
      LEFT JOIN begegnungen b ON t.id = b.gewinner  
      GROUP BY t.id  
     ) tab  
ORDER BY 2 DESC, 1  
;

Ziemlicher Brummer, die Abfrage, so ne große hatte ich noch nie :-)
Laufzeit ist aber noch sehr in Ordnung.

[Ilja]

yo,

platzierung  teilnehmer  punkte
1            A           15
2            B           15
3            C           10
4            D           8

wenn ich mike richtig verstanden habe, will er aber die teilnehmer mit gleicher punktzahl auch auf den gleichen platz haben. insofern würde auch nur die sortierung nach der punktezahl reichen, aber aus präsentationsgründen würde ich noch ein zusätzliches kriterium mit rein nehmen, damit die tabelle bei mehrfachen aufrufen immer gleich aussieht. aber teilnhemer B sollte zusammen mit teilnehmer A auch auf platz 1 sein. und meiner meinung reicht dazu in der korrelierten unterabfrage ein einfacher vergleich, wer mehr punkte hat.

SELECT (SELECT COUNT(*)
        FROM tabelle t2
        WHERE t2.punkte > t1.punkt
       ) + 1 AS platzierung,
  teilnehmer,
  punkte
FROM tabelle t1
ORDER BY punkte DESC, teilnehmer
;

vielleicht noch der hinweis, dass unterabfragen bei mysql erst ab der version 4.1 und höher gehen.

Ilja

[ChrisB]

Hi,

platzierung  teilnehmer  punkte
1            A           15
2            B           15
3            C           10
4            D           8

wenn ich mike richtig verstanden habe, will er aber die teilnehmer mit gleicher punktzahl auch auf den gleichen platz haben.

Dann sollte er in seinem Beispiel nicht den beiden mit 15 Punkten zwei unterschiedliche Platzierungen geben ...

MfG ChrisB