Page 1 of 1

SOLVED: Getting the two best results

Posted: Mon Sep 03, 2007 5:29 pm
by MarK (CZ)
Hi :)

I have a table `results` cca like this:

Code: Select all

player_id   race_id   stage_id   time
    1          1          1        5
    1          1          2        3
    1          2          1        4
    1          2          2        6
    1          2          3        2
    1          3          1        7
    1          3          2        2
    2          1          1        2
    2          1          2        3
    2          2          1        7
    2          2          2        2
    2          2          3        7
    2          3          1        5
    2          3          2        4
    ...
(That means there are many races, each consists of a few stages and the final time of the race is the sum of all stages times)

I may get the final times with

Code: Select all

SELECT *, SUM(time) as time_final,
FROM results
WHERE race_id = '$id'
GROUP BY player_id, race_id
Now, I need to get the two fastest times from the season for every player. Is there some easy way to do that via MySQL?

Thanks in advance

Posted: Mon Sep 03, 2007 10:38 pm
by s.dot
ORDER BY `time_final` DESC LIMIT 0,2

:?:

Posted: Tue Sep 04, 2007 3:47 am
by MarK (CZ)
scottayy: That way I can get the two best results for one player... but to get a list of all players + two best results?

Posted: Tue Sep 04, 2007 5:14 am
by timvw
A decent sql dbms can do the following:

Code: Select all

SELECT *
FROM laptimes AS l1
WHERE lap_id IN (
 SELECT lap_id
 FROM laptimes AS l2
 WHERE l1.player_id = l2.player_id
 ORDER BY laptime ASC
 LIMIT 3
)
ORDER BY player_id ASC, laptime ASC;

(it would return the best 3 laptimes for each players)

Posted: Tue Sep 04, 2007 5:39 am
by s.dot
I believe mysql 4.4+ can do subqueries like timvw posted.

Posted: Tue Sep 04, 2007 6:05 am
by vinoth
ya the sub query will return the result

Posted: Tue Sep 04, 2007 6:15 am
by MarK (CZ)
Yeah, I've already managed it with subqueries just before you've written it :D
Thanks a lot for the help!