ich habe folgende Tabelle: +---------+--------+ | spieler | punkte | +---------+--------+ | Anton | 10 | | Anton | 150 | <-- | Bert | 20 | | Bert | 25 | | Claudia | 30 | | Claudia | 35 | <-- | Dora | 40 | | Dora | 45 | <-- +---------+--------+
Jeder Spieler kann 0 bis n mal spielen. Ich versuche vergeblich, die oben markierten Top 3 Spieler mit den meisten Punkten in einem Spiel mit einer Abfrage zu holen.
SELECT spieler, punkte FROM spiele WHERE 1 GROUP BY spieler ORDER BY punkte DESC LIMIT 3
resultiert in: +---------+--------+ | spieler | punkte | +---------+--------+ | Dora | 40 | | Claudia | 30 | | Bert | 20 | +---------+--------+
Ich fand die Erklärung dazu im Manual hilfreich, sich die Ergebniszeilen nicht als Treffer, sondern als Representation aller Zeilen der jeweiligen Gruppe vorzustellen. Was ich dort nicht fand war: Gibt es einen - und wenn, welchen - Königsweg, das Problem zu lösen? Eventuell kann man die sortierte Liste gruppieren statt die gruppierte Liste sortieren?
> Jeder Spieler kann 0 bis n mal spielen. Ich versuche vergeblich, die > oben markierten Top 3 Spieler mit den meisten Punkten in einem Spiel mit > einer Abfrage zu holen.
> […] Gibt es einen - und wenn, welchen - Königsweg, das Problem zu lösen? > Eventuell kann man die sortierte Liste gruppieren statt die gruppierte > Liste sortieren?
> Jeder Spieler kann 0 bis n mal spielen. Ich versuche vergeblich, die > oben markierten Top 3 Spieler mit den meisten Punkten in einem Spiel mit > einer Abfrage zu holen.
> […] Gibt es einen - und wenn, welchen - Königsweg, das Problem zu lösen? > Eventuell kann man die sortierte Liste gruppieren statt die gruppierte > Liste sortieren?
Wenn Du das Ergebnis näher betrachtest, fällt Dir auf, dass die gruppierten Punkte immer der Punkte des ersten gespeicherten Spiels eines Spielers entsprechen. Deshalb gibt es auch keinen Datensatz für Anton, obwohl er in einem zweiten Spiel mehr Punkte als alle anderen erzielt hat. Daraus folgt:
SELECT * FROM ( SELECT spieler, punkte FROM spiele ORDER BY punkte DESC ) AS foo GROUP BY spieler LIMIT 3
> * Thomas 'PointedEars' Lahn <PointedE...@web.de> wrote: >> [...] >> SELECT * >> FROM ( >> SELECT spieler, punkte >> FROM spiele >> ORDER BY punkte DESC >> ) AS foo >> GROUP BY spieler >> LIMIT 3
> Sub-SELECTs hab ich bisher immer ausgeblendet. Wie dumm von mir. > Vielen Dank!
Die sind hier nicht nötig!
select spieler, max(punkte) as punkte from spiele group by spieler order by punkte desc limit 3;
+---------+--------+ | spieler | punkte | +---------+--------+ | anton | 150 | | dora | 45 | | claudia | 35 | +---------+--------+ 3 rows in set (0.00 sec)
Christian Welzel wrote: > Am 03.04.2011 20:23, schrieb Jens Fischer: >> * Thomas 'PointedEars' Lahn <PointedE...@web.de> wrote: >>> [...] >>> SELECT * >>> FROM ( >>> SELECT spieler, punkte >>> FROM spiele >>> ORDER BY punkte DESC >>> ) AS foo >>> GROUP BY spieler >>> LIMIT 3
>> Sub-SELECTs hab ich bisher immer ausgeblendet. Wie dumm von mir. >> Vielen Dank!
> Die sind hier nicht nötig!
> select spieler, max(punkte) as punkte > from spiele > group by spieler > order by punkte desc > limit 3;
> +---------+--------+ > | spieler | punkte | > +---------+--------+ > | anton | 150 | > | dora | 45 | > | claudia | 35 | > +---------+--------+ > 3 rows in set (0.00 sec)
Ja, das ist die bessere Lösung.
Ausserdem wäre hier etwas Normalisierung angebracht, d.h. eine spieler- Tabelle mit Primary Key, und eine spieler_id-Spalte mit Foreign Keys auf diese Tabelle.
MyISAM:
CREATE TABLE `spieler` ( `spieler_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `vorname` VARCHAR(50) NOT NULL ) ENGINE=MyISAM;
INSERT INTO `spieler` (`vorname`) SELECT DISTINCT `spieler` FROM `spiele`;
ALTER TABLE `spiele` ADD COLUMN `spieler_id` INT(11) UNSIGNED NOT NULL AFTER `spieler`;
UPDATE `spiele`, `spieler` SET `spiele`.`spieler_id`=`spieler`.`spieler_id` WHERE `spiele`.`spieler` = `spiele`.`vorname`;
ALTER TABLE `spiele` DROP COLUMN `spieler`;
InnoDB (empfohlen):
CREATE TABLE spieler ( `spieler_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `vorname` VARCHAR(50) NOT NULL ) ENGINE=InnoDB;
INSERT INTO `spieler` (`vorname`) SELECT DISTINCT `spieler` FROM `spiele`;
ALTER TABLE `spiele` ADD COLUMN `spieler_id` INT(11) UNSIGNED NOT NULL AFTER `spieler`;
UPDATE `spiele`, `spieler` SET `spiele`.`spieler_id`=`spieler`.`spieler_id` WHERE `spiele`.`spieler` = `spieler`.`vorname`;
ALTER TABLE `spiele` ADD CONSTRAINT `spieler_id` FOREIGN KEY `fk_spieler_id1` (`spieler_id`) REFERENCES `spieler` (`spieler_id`) ON DELETE CASCADE ON UPDATE NO ACTION, ENGINE=InnoDB;
ALTER TABLE `spiele` DROP COLUMN `spieler`;
Nach der Anpassung sieht die Abfrage z.B. so aus:
SELECT `spieler`.`vorname`, MAX(`spiele`.`punkte`) AS `punkte` FROM `spiele` LEFT JOIN `spieler` USING (`spieler_id`) GROUP BY `spieler`.`spieler_id` ORDER BY `punkte` DESC LIMIT 3;
* Thomas 'PointedEars' Lahn <PointedE...@web.de> wrote:
> Ausserdem wäre hier etwas Normalisierung angebracht, d.h. eine spieler- > Tabelle mit Primary Key, und eine spieler_id-Spalte mit Foreign Keys auf > diese Tabelle. > [...]
Ich hab zu schlecht vereinfacht: Es geht an sich um die Top 10 Anbieter mit den bestbewerteten Produkten. Anbieter-->Produkte: 1-->n. Da ich sowieso die Anbieter-IDs benötige, führe ich die Abfrage gleich in der Produktetabelle aus:
> select spieler, max(punkte) as punkte > from spiele > group by spieler > order by punkte desc > limit 3;
> +---------+--------+ > | spieler | punkte | > +---------+--------+ > | anton | 150 | > | dora | 45 | > | claudia | 35 | > +---------+--------+
Ich hatte im Hinterkopf, das vorher schonmal erfolglos probiert zu haben und hab es auch in einem Test-Script wiedergefunden, allerdings ohne das Alias "AS punkte" (analog zu diesem Beispiel).
Warum ist das Ergebnis von:
SELECT spieler, MAX(punkte) FROM spiele GROUP BY spieler ORDER BY punkte desc LIMIT 3;
> * Christian Welzel <gaw...@camlann.de> wrote: >> select spieler, max(punkte) as punkte >> from spiele >> group by spieler >> order by punkte desc >> limit 3;
> [...]
> SELECT spieler, MAX(punkte) > FROM spiele > GROUP BY spieler > ORDER BY punkte desc > LIMIT 3;
> [...]
> Wenn das Weglassen des Alias bei Ausdrücken nicht zulässig ist, würde > ich einen Fehler statt eines anderen Verhaltens erwarten. Liege ich da > falsch?
Es ist zulässig, also kein Fehler, das Feld »punkte« im zweiten Beispiel existiert ja in der Tabelle »spieler«. Im ersten Beispiel wird aber nach »MAX(punkte) AS punkte«, sortiert, nicht nach dem Tabellenfeld »punkte«.
> Es ist zulässig, also kein Fehler, das Feld »punkte« im zweiten Beispiel > existiert ja in der Tabelle »spieler«. Im ersten Beispiel wird aber nach > »MAX(punkte) AS punkte«, sortiert, nicht nach dem Tabellenfeld »punkte«.
>> ich habe folgende Tabelle: >> +---------+--------+ >> | spieler | punkte | >> +---------+--------+ >> | Anton | 10 | >> | Anton | 150 | <-- >> | Bert | 20 | >> | Bert | 25 | >> | Claudia | 30 | >> | Claudia | 35 | <-- >> | Dora | 40 | >> | Dora | 45 | <-- >> +---------+--------+
>> Jeder Spieler kann 0 bis n mal spielen. Ich versuche vergeblich, die >> oben markierten Top 3 Spieler mit den meisten Punkten in einem Spiel mit >> einer Abfrage zu holen.
>> […] Gibt es einen - und wenn, welchen - Königsweg, das Problem zu lösen? >> Eventuell kann man die sortierte Liste gruppieren statt die gruppierte >> Liste sortieren?
> Wenn Du das Ergebnis näher betrachtest, fällt Dir auf, dass die gruppierten > Punkte immer der Punkte des ersten gespeicherten Spiels eines Spielers > entsprechen. Deshalb gibt es auch keinen Datensatz für Anton, obwohl er in > einem zweiten Spiel mehr Punkte als alle anderen erzielt hat. Daraus folgt:
Deine Feststellung das immer 'erste' Datensatz benutzt wird, trifft nur manchmal zu. Allerdings nur zufällig, in Abhängigkeit der verwendeten Engine. Wir arbeiten in der Datenbankerei mit Mengen, und nicht mit Reihenfolgen.
> SELECT * > FROM ( > SELECT spieler, punkte > FROM spiele > ORDER BY punkte DESC > ) AS foo > GROUP BY spieler > LIMIT 3
Auch bei diesem Beispiel könnte sich die Engine für irgendeinen Datensatz der inneren Menge entschließen. Das verhalten ist schlichtweg nicht definiert!
Des weiteren rate ich pauschal vom Einsatz von unperformanten Subselects ab, solange es vermeidbar ist.
Harald Stowasser wrote: > Am 03.04.2011 13:28, schrieb Thomas 'PointedEars' Lahn: >> Jens Fischer wrote: >>> ich habe folgende Tabelle: >>> +---------+--------+ >>> | spieler | punkte | >>> +---------+--------+ >>> | Anton | 10 | >>> | Anton | 150 | <-- >>> | Bert | 20 | >>> | Bert | 25 | >>> | Claudia | 30 | >>> | Claudia | 35 | <-- >>> | Dora | 40 | >>> | Dora | 45 | <-- >>> +---------+--------+
>>> Jeder Spieler kann 0 bis n mal spielen. Ich versuche vergeblich, die >>> oben markierten Top 3 Spieler mit den meisten Punkten in einem Spiel mit >>> einer Abfrage zu holen.
>>> […] Gibt es einen - und wenn, welchen - Königsweg, das Problem zu lösen? >>> Eventuell kann man die sortierte Liste gruppieren statt die gruppierte >>> Liste sortieren? >> Wenn Du das Ergebnis näher betrachtest, fällt Dir auf, dass die >> gruppierten Punkte immer der Punkte des ersten gespeicherten Spiels eines >> Spielers entsprechen. Deshalb gibt es auch keinen Datensatz für Anton, >> obwohl er in einem zweiten Spiel mehr Punkte als alle anderen erzielt >> hat. Daraus folgt:
> Deine Feststellung das immer 'erste' Datensatz benutzt wird, trifft nur > manchmal zu.
Sie trifft allerdings hier zu.
> Allerdings nur zufällig, in Abhängigkeit der verwendeten Engine.
Bei jeder Engine, die MySQL unterstützt, ist das so:
,-<http://dev.mysql.com/doc/refman/5.0/en/select.html> | | If you use GROUP BY, output rows are sorted according to the GROUP BY | columns as if you had an ORDER BY for the same columns. To avoid the | overhead of sorting that GROUP BY produces, add ORDER BY NULL: | | SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
> Wir arbeiten in der Datenbankerei mit Mengen, und nicht mit > Reihenfolgen.
Oh danke, das wusste ich noch gar nicht :->
>> SELECT * >> FROM ( >> SELECT spieler, punkte >> FROM spiele >> ORDER BY punkte DESC >> ) AS foo >> GROUP BY spieler >> LIMIT 3
> Auch bei diesem Beispiel könnte sich die Engine für irgendeinen > Datensatz der inneren Menge entschließen. Das verhalten ist schlichtweg > nicht definiert!
Das ist hcslaf.
> Des weiteren rate ich pauschal vom Einsatz von unperformanten Subselects > ab, solange es vermeidbar ist.
Natürlich. Wäre mir gleich die Möglichkeit ohne Subselect eingefallen, so hätte ich sie stattdessen gepostet.
Bitte keine Cc: (schon gar nicht zweimal!); ich lese die Gruppen in denen ich poste.
>>>> [...] >>> Wenn Du das Ergebnis näher betrachtest, fällt Dir auf, dass die >>> gruppierten Punkte immer der Punkte des ersten gespeicherten Spiels eines >>> Spielers entsprechen. Deshalb gibt es auch keinen Datensatz für Anton, >>> obwohl er in einem zweiten Spiel mehr Punkte als alle anderen erzielt >>> hat. Daraus folgt:
>> Deine Feststellung das immer 'erste' Datensatz benutzt wird, trifft nur >> manchmal zu.
> Sie trifft allerdings hier zu.
>> Allerdings nur zufällig, in Abhängigkeit der verwendeten Engine.
> Bei jeder Engine, die MySQL unterstützt, ist das so:
> ,-<http://dev.mysql.com/doc/refman/5.0/en/select.html> > | > | If you use GROUP BY, output rows are sorted according to the GROUP BY > | columns as if you had an ORDER BY for the same columns. To avoid the > | [...]
Wo siehst du hier die Definition, dass in Jens Query
| SELECT spieler, punkte | FROM spiele WHERE 1 | GROUP BY spieler ORDER BY punkte DESC LIMIT 3
das "erste" Spiel *pro Spieler* selektiert wird?
+---------+--------+ | Dora | 40 | | Dora | 45 | +---------+--------+
Ein automatisches "ORDER BY spieler" definiert hier überhaupt nicht, dass im Ergebnis
+---------+--------+ | Dora | 40 | +---------+--------+
und nicht
+---------+--------+ | Dora | 45 | +---------+--------+
auftaucht.
>>> SELECT * >>> FROM ( >>> SELECT spieler, punkte >>> FROM spiele >>> ORDER BY punkte DESC >>> ) AS foo >>> GROUP BY spieler >>> LIMIT 3
>> Auch bei diesem Beispiel könnte sich die Engine für irgendeinen >> Datensatz der inneren Menge entschließen. Das verhalten ist schlichtweg >> nicht definiert!
> Das ist hcslaf.
Nein, das ist korrekt, weil das hier auf genau dasselbe hinausläuft...
>>>>> [...] >>>> Wenn Du das Ergebnis näher betrachtest, fällt Dir auf, dass die >>>> gruppierten Punkte immer der Punkte des ersten gespeicherten Spiels >>>> eines Spielers entsprechen. Deshalb gibt es auch keinen Datensatz für >>>> Anton, obwohl er in einem zweiten Spiel mehr Punkte als alle anderen >>>> erzielt hat. Daraus folgt:
>>> Deine Feststellung das immer 'erste' Datensatz benutzt wird, trifft nur >>> manchmal zu.
>> Sie trifft allerdings hier zu.
>>> Allerdings nur zufällig, in Abhängigkeit der verwendeten Engine.
>> Bei jeder Engine, die MySQL unterstützt, ist das so:
>> ,-<http://dev.mysql.com/doc/refman/5.0/en/select.html> >> | >> | If you use GROUP BY, output rows are sorted according to the GROUP BY >> | columns as if you had an ORDER BY for the same columns. To avoid the >> | [...]
> Wo siehst du hier die Definition, dass in Jens Query
> | SELECT spieler, punkte > | FROM spiele WHERE 1 > | GROUP BY spieler ORDER BY punkte DESC LIMIT 3
> das "erste" Spiel *pro Spieler* selektiert wird?
> +---------+--------+ > | Dora | 40 | > | Dora | 45 | > +---------+--------+
> Ein automatisches "ORDER BY spieler" definiert hier überhaupt nicht, > dass im Ergebnis
> +---------+--------+ > | Dora | 40 | > +---------+--------+
> und nicht
> +---------+--------+ > | Dora | 45 | > +---------+--------+
> auftaucht.
>>>> SELECT * >>>> FROM ( >>>> SELECT spieler, punkte >>>> FROM spiele >>>> ORDER BY punkte DESC >>>> ) AS foo >>>> GROUP BY spieler >>>> LIMIT 3
>>> Auch bei diesem Beispiel könnte sich die Engine für irgendeinen >>> Datensatz der inneren Menge entschließen. Das verhalten ist schlichtweg >>> nicht definiert!
>> Das ist hcslaf.
> Nein, das ist korrekt, weil das hier auf genau dasselbe hinausläuft...
| By default, MySQL extends the use of GROUP BY so that the select list can | refer to nonaggregated columns not named in the GROUP BY clause. This ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | means that the preceding query is legal in MySQL.
(Aber zum Beispiel nicht zulässig in Microsoft Jet SQL [Access], wie ich aus eigener leidvoller Erfahrung weiss. Einmal GROUP BY, immer GROUP BY.)
| You can use this feature to get better performance by avoiding unnecessary | column sorting and grouping. However, this feature is useful primarily | when all values in each nonaggregated column not named in the GROUP BY are | the same for each group. The server is free to return any value from each ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | group, so unless they are the same, the values chosen are indeterminate. ^^^^^ | Furthermore, the selection of values from each group cannot be influenced | by adding an ORDER BY clause. Sorting of the result set occurs after | values have been chosen, and ORDER BY does not affect which values the | server chooses.
Richtig ist daher in der Tat, dass weder ein vorgängiges ORDER BY in der Unterabfrage (wie ich vorschlug) noch ein nachträgliches ORDER BY hier weiterhilft.
Gut, dass wir das geklärt haben.
-- PointedEars
Bitte keine Kopien per E-Mail. / Please do not Cc: me.
