Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
MarK (CZ)
Forum Contributor
Posts: 239 Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:
Post
by MarK (CZ) » Mon Sep 03, 2007 5:29 pm
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.
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Mon Sep 03, 2007 10:38 pm
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.
MarK (CZ)
Forum Contributor
Posts: 239 Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:
Post
by MarK (CZ) » Tue Sep 04, 2007 3:47 am
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 » Tue Sep 04, 2007 5:14 am
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)
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Tue Sep 04, 2007 5:39 am
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 » Tue Sep 04, 2007 6:05 am
ya the sub query will return the result
MarK (CZ)
Forum Contributor
Posts: 239 Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:
Post
by MarK (CZ) » Tue Sep 04, 2007 6:15 am
Yeah, I've already managed it with subqueries just before you've written it
Thanks a lot for the help!