CODEWITHSUNDEEP
mysqlsqlmariadb
exexe
exexe

Reputation: 196

Is it possible to make this query working properly?

I'm creating a simple game and I want to get the best lap_time for each type in the db.
However, my query returns the wrong player_id (3 in second row) and total_ranks (all ranks instead of count by type).

Link to sqlfiddle: http://sqlfiddle.com/#!9/a0c36a/2

Desired result 

+--------+-----+-------+------------+----------------+-------------+
| level  | cp  | type  | player_id  | MIN(lap_time)  | total_ranks |
+--------+-----+-------+------------+----------------+-------------+
|     1  |  1  |    0  |         1  |          10.5  |           4 |
|     1  |  1  |    1  |         2  |         10.45  |           3 |
+--------+-----+-------+------------+----------------+-------------+

Is it possible to make it work in 1 query or do I need at least 2?

Upvotes: 0

Views: 64

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One canonical way to solve this problem in MySQL is to use a subquery to identify the minimum lap time for each type. Then join your full table to this to obtain the entire record. Note that a nice side effect of this approach is that we also get back ties if a given type have more than one person sharing the minimum lap time.

SELECT r1.*, r2.total_ranks
FROM runtimes r1
INNER JOIN
(
    SELECT type, MIN(lap_time) AS min_lap_time, COUNT(*) AS total_ranks
    FROM runtimes
    GROUP BY type
) r2
    ON r1.type     = r2.type AND
       r1.lap_time = r2.min_lap_time

Here is a link to your updated Fiddle:

SQLFiddle

Upvotes: 2

Aaron Dietz
Aaron Dietz

Reputation: 10277

Fiddle

Same concept as Tim, but with Total_Ranks column

SELECT level, cp, R.type, player_id, MinTime, Total_Ranks
FROM runtimes R
JOIN (SELECT TYPE, MIN(LAP_TIME) MinTime, Count(*) Total_Ranks
      FROM RUNTIMES
      GROUP BY TYPE) T on R.Type = T.Type
                      and R.lap_time = T.MinTime
WHERE level=1 
AND cp=1

Upvotes: 2

Related Questions