SOLVED: Getting the two best results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

SOLVED: Getting the two best results

Post 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
Last edited by MarK (CZ) on Tue Sep 04, 2007 6:15 am, edited 1 time in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

ORDER BY `time_final` DESC LIMIT 0,2

:?:
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I believe mysql 4.4+ can do subqueries like timvw posted.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
vinoth
Forum Contributor
Posts: 113
Joined: Thu Aug 02, 2007 3:08 am
Location: India
Contact:

Post by vinoth »

ya the sub query will return the result
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

Yeah, I've already managed it with subqueries just before you've written it :D
Thanks a lot for the help!
Post Reply