[MySQL] Beste tijd per persoon uit een tabel halen

[Oogst]

Ik heb een database met daarin de tijden die spelers van een game per race hebben gehaald. Op het moment pak ik gewoon de snelste tien tijden daaruit om aan spelers te tonen, maar het resultaat is dat ik vaak van de beste speler tien tijden krijg. Het zou cooller zijn als ik van de beste tien spelers ieder 1 tijd zou krijgen. Maar hoe kan ik een query daarvoor doen in MySQL met PHP?

Ik heb nu een query zoals deze:

code:

SELECT playerName, time
    FROM ProunHighscores
    WHERE trackName = '$trackName'
    ORDER BY time ASC
    LIMIT 0, 10

Ik heb een variabele computerID om te bepalen welke computer er submit. Wat ik dus eigenlijk zou willen, is alleen unieke [i$computerID[/i]'s, met per speler specifiek zijn snelste tijd. In pseudo-code is dat dus eigenlijk als volgt:

code:

SELECT playerName, time
    FROM ProunHighscores
    WHERE trackName = '$trackName'
    ORDER BY time ASC
    LIMIT 0, 10
    EN per computerID alleen de kleinste time

Kunnen jullie me uitleggen concreet hoe zo'n query te doen in MySQL?

Alvast bedankt!

[BSTNjitRam]

Zoek eens op wat GROUP BY doet

[Oogst]

Bedankt voor de razendsnelle reply! Dat blijkt inderdaad best simpel!

Echter, ik krijg nu alleen niet de playerName die bij de snelste tijd hoort: onder dezelfde computerID kunnen vele namen schuil gaan en ik wil graag de naam die hoort bij de snelste tijd. Ik krijg nu een playerName die wel hoort bij die computerID, maar niet perse ook bij die tijd. Dit is wat ik nu heb:

code:

SELECT playerName, MIN(time) AS time
    FROM ProunHighscores
    WHERE trackName = '$trackName'
    GROUP BY computerID
    ORDER BY time ASC
    LIMIT 0, 10

Hoe krijg ik de playerName die bij MIN(time) hoort?

[ Voor 3% gewijzigd door Oogst op 17-07-2011 21:18 ]

[BSTNjitRam]

Dit zou in MSSQL bijvoorbeeld al niet werken. Je vraagt een veld op waar je niet op groepeert en waar je geen aggregate function van opvraagt. MySQL laat dit (helaas) wel toe.

Verder vraag ik me af waarom je groepeert op ComputerID terwijl je per speler de snelste tijd wilt hebben?

[Oogst]

Spelers kunnen per tijd een andere naam opgeven en dat zou het een beetje lame maken, dus ik doe per install.

Maareuh, hoe moet ik dit wel doen dan? Sowieso is het MySQL, dus wat dat betreft maakt het me niet echt uit of andere SQL implementaties het wel of niet toestaan. Hoe zou ik dit wel moeten doen, en hoe krijg ik er dan de juiste playerName bij?

[NMe]

Je zou kunnen beginnen met het lezen van de FAQ.
Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

[Oogst]

Ah, kijk, ik had ergens anders documentatie vandaan getoverd (w3schools.com), maar dit is duidelijker, bedankt voor de link!

Maar ik zie nog steeds niet hoe ik mijn query nu op moet bouwen. Als ik je link goed begrijp, is het probleem in mijn geval niet dat computerID niet in de SELECT voorkomt, maar dat ik geen aggregate functie op playerName loslaat. Ik zit hier echter even door de lijst van aggregate functies te kijken, maar die lijken allemaal op playerName zelf betrekking te zullen gaan hebben, terwijl ik de playerName die bij die MIN(time) hoort wil hebben. Hoe doe ik dat?

[Voutloos]

1. w3schools.com moet je niet komen voor mysql. Sterker nog, ik zou het voor de rest inc. html ook willen afraden, want het is gewoon een ruksite.
2. Voor syntax vragen begin je gewoon in de manual http://dev.mysql.com/doc/refman/5.1/en/select.html

Overigens noem je bijna letterlijk het antwoord. Je hebt reeds gezegd dat je naam en minimum tijd wilt selecteren uit alle highscores, dat je wilt groeperen op id en dat je wilt ordenen op basis van het minimum. Vertaal voorgaande zin naar Engels en je query staat er gewoon letterlijk.

[Dido]

Voutloos schreef op zondag 17 juli 2011 @ 22:27:
Overigens noem je bijna letterlijk het antwoord. Je hebt reeds gezegd dat je naam en minimum tijd wilt selecteren uit alle highscores, dat je wilt groeperen op id en dat je wilt ordenen op basis van het minimum. Vertaal voorgaande zin naar Engels en je query staat er gewoon letterlijk.

Ik denk het niet.

Als ik eea goed begrijp ligt het iets ingewikkelder.

(TS, verbeter me als ik fout zit!)
Voorbeelddata zou kunnen zijn:
123, 12 seconden, Piet
123, 10 seconden, Jan

De voorbeeldqueries waarbij je groepeert op ID, en min(tijd) erbij plakt, geven geen naam. Ga je de naam opnemen in de slect, zonder aggegate op naam slikt MySQL het gek genoeg wel, maar kan je resultaat worden dat je 123, 10 seconden, Piet terugkrijgt, wat nergens op slaat.

Ik vermoed dat je er niet onderuit komt om een subquery te gebruiken om de juiste naam erbij te krijgen (iets als WHERE name = select x.name from tabel x where x.id = id and x.tijd = min(tijd) ).

[Voutloos]

Oogst schreef op zondag 17 juli 2011 @ 20:15:
Ik heb een variabele computerID om te bepalen welke computer er submit. Wat ik dus eigenlijk zou willen, is alleen unieke [i$computerID[/i]'s, met per speler specifiek zijn snelste tijd.

Dit stukje klinkt meer als groeperen op basis van id en naam, en klaar is ts.

Mocht Dido de interpretatie juist hebben: Zoekterm voor dergelijke query is 'groupwise maximum'.

Oogst schreef op zondag 17 juli 2011 @ 21:56:
Sowieso is het MySQL, dus wat dat betreft maakt het me niet echt uit of andere SQL implementaties het wel of niet toestaan.

Dit geval zou je wel moeten uit maken, want het is feitelijk fout dat mysql het toestaat, en je hebt zelf al ervaren waarom: Je kreeg een random naam.

[ Voor 28% gewijzigd door Voutloos op 17-07-2011 22:45 ]

[Precision]

Ik weet niet wat de bedoeling net is, maar houdt er rekening mee dat als het de 10 beste tijden is die je wilt dat dit niet noodzakelijk 10 personen zijn. Wat doe je als je meerdere personen binnen dezelfde tijd hebt? Kijk je dan naar een andere parameter?

Let op volgens mij is time een function in mysql, dus: `time`

Edit: Zoiets is dit dan, waarschijnlijk niet wat je zoekt, maar misschien dat het nog handig is voor iets anders.

MySQL:

select playerName, `time` from ProunHighscores s1
where 9 >= (select count(discinct `time`) from ProunHighscores s2 where s1.`time` < s2.`time` and trackName = 'bla') and trackName = 'bla'
order by `time` ASC

Edit 2: ik moet leren typen, ontkenning vergeten

[ Voor 32% gewijzigd door Precision op 17-07-2011 23:37 ]

[jip_86]

SQL:

select h.playerName, h.time
from ProunHighsores h
where h.trackname = '$trackName' 
and h.time = 
(
  select min(i.time) as time 
  from ProunHighscores i 
  where i.computerId = h.computerId
)
limit 0, 10

Weet of het zo direct werkt in MySQL, maar iets vergelijkbaars gebruik ik momenteel op het werk heel veel. Daar gaat het om een tabel waar als het ware een soort versiehistorie op de records zit en ik per id dus alleen de nieuwste uit de historie wil hebben.

[Oogst]

Voutloos schreef op zondag 17 juli 2011 @ 22:41:
...
Zoekterm voor dergelijke query is 'groupwise maximum'.
...

Ik was even een paar dagen zonder internet, dus kon het niet gelijk uitproberen, maar nu had ik eindelijk de kans om even te gaan zoeken, en het is gelukt nu! Die "groupwise maximum" was idd precies de term die ik zocht. Dit is mijn uiteindelijke query geworden:

code:

SELECT table1.playerName, table1.time
    FROM ProunHighscores AS table1,
        (SELECT computerID, MIN(time) as time
            FROM ProunHighscores
            WHERE trackName = '$trackName'
            GROUP BY computerID) AS table2
    WHERE table1.trackName = '$trackName'
        AND table1.time = table2.time
        AND table1.computerID = table2.computerID
    ORDER BY time ASC
    LIMIT 0, 10

Bedankt allemaal voor de hulp!

[Oogst]

Hmm, toch nog een vraagje. Ik wil ook bepalen hoeveel snellere tijden ik in mijn database heb, en daar krijg ik een raar resultaat. Ik heb daar nu deze query gemaakt:

code:

    $result = mysql_query("
                            SELECT COUNT(*)+1 AS ranking
                                FROM (SELECT MIN(time) AS minimumTime
                                        FROM ProunHighscores
                                        GROUP BY computerID
                                        WHERE trackName = '$trackName'
                                            AND difficulty = '$difficulty'
                                            AND time < $time) AS table1
                          ");
    $ranking = mysql_result($result, 0, "ranking");

Echter, dit levert me een warning op, en ik krijg geen getal terug: "Warning: mysql_result() expects parameter 1 to be resource, boolean given". Hij verwijst daarin naar de ranking regel. Ik heb op een online test-omgeving zitten klooien met vergelijkbare queries en daar ging dat wel goed. De oude query, zonder GROUP BY, deed het ook wel gewoon zoals ik wilde. Ter referentie:

code:

    $result = mysql_query("
                            SELECT COUNT(*)+1 AS ranking
                                FROM ProunHighscores
                                WHERE time < $time
                                    AND trackName = '$trackName'
                                    AND difficulty = '$difficulty'
                          ");
    $ranking = mysql_result($result, 0, "ranking");

Heeft iemand enig idee waarom die oude wel goed ging en die nieuwe niet?

[Oogst]

*bump*

Iemand enig idee voor die laatste vraag?

[_js_]

Lees de foutmelding, blijkbaar is het eerste argument een boolean ipv een resource. Dat argument komt rechtstreeks uit mysql_query, dus de volgende stap is kijken in welke omstandigheden mysql_query een boolean teruggeeft.

quote: PHP Handleiding

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

Blijkbaar is er dus iets fout gegaan, nu mag je zelf gaan uitzoeken hoe je de foutmelding die daarbij hoort vindt.

[Oogst]

Ik had zelf ook al gesnapt dat de MySQL query fout is en dat er daarom een boolean uit komt. Het punt is juist dat ik niet zie wat er mis is met de query en dat ik met dingen veranderen aan de query ook niet voor elkaar krijg dat hij het wel gaat doen.

[Creepy]

edit: * Creepy is een blinde vink.....
edit: nee, toch niet

Welke foutmelding geeft MySQL je dan? Als mysql_query false teruggeeft, dump dan op z'n minst de foutmelding even naar je scherm zodat je kan zien waarom je false terugkrijgt. Domweg gaan lopen gokken wat er mis is gaat je niet echt helpen nee.

[ Voor 182% gewijzigd door Creepy op 26-07-2011 10:23 ]

[Voutloos]

http://nl3.php.net/manual/en/function.mysql-query.php
Voorbeeld #1 in de documentatie laat zien dat je mysql_error() kan gebruiken om de error message te achterhalen. Of als je uberhaupt de lijst mysql_* functies doorneemt zou je mysql_error() moeten vinden.

Dus welke error treedt er nu op?

Bonus vervolghint: Uit de error zal blijken dat je syntax niet goed is, en dat je kan nalezen hoe het wel moet in Select syntax doc.

[ Voor 24% gewijzigd door Voutloos op 26-07-2011 10:29 ]

[Oogst]

Ah, ik dacht dus dat SQL zelf errors al printe, net als PHP dat doet. Met een echo mysql_error() erbij kreeg ik opeens wel zinnige feedback.

Het probleem was dat GROUP BY na WHERE moet komen. Ik wist niet dat volgorde belangrijk was, weer wat geleerd!