COUNT and GROUP BY min and max using INNER Join

Question

Sorry if title is confusing, I have a table that stores a player and how long it took them to complete the map in seconds, I am trying to write a query that selects all the maps that a specific player has the fastest/shortest time

I tried this query:

SELECT * 
  FROM ck_playertimes a 
  JOIN 
     ( SELECT MIN(runtimepro) runtimepro 
         FROM ck_playertimes 
        WHERE style = 0 
        group 
           by mapname
     ) b 
    ON a.runtimepro = b.runtimepro 
 WHERE steamid = 'STEAM_1:0:20019070' 
   AND style = '0'

However it is selecting some times that are not the shortest/fastest for a specific map and there's even a duplicate somehow when that duplicate does not exist in the table

Any help? =)

Hi. This is a faq. There is even a tag: greatest-n-per-group. Force yourself to write a question/problem/goal many ways clearly, with & without your particular string/names & google. — philipxy, Oct 17 '18 at 00:17
Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — philipxy, Oct 17 '18 at 00:19

score 1 · Accepted Answer · answered Oct 16 '18 at 14:57

The subquery to which you join should find the minimum run time for each player. So, the following should work:

SELECT c1.*
FROM ck_playertimes c1
INNER JOIN
(
    SELECT mapname, MIN(runtimepro) AS min_runtimepro
    FROM ck_playertimes
    GROUP BY mapname
) c2
    ON c1.mapname = c2.mapname AND c1.runtimepro = c2.min_runtimepro
WHERE
    c1.steamid = 'STEAM_1:0:20019070' AND
    c1.style = '0';

If you are using MySQL 8+ or later, another, more modern way of writing the above query uses ROW_NUMBER:

SELECT *       -- but always better to list out explicit columns
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY mapname ORDER BY runtimepro) rn
    FROM ck_playertimes
) t
WHERE rn = 1;

score 0 · Answer 2 · answered Oct 16 '18 at 15:06

What you are asking is min and max time, for each player, for each map This is a basic select with group by, with bo join needed

I assumed that the table you select from holds a player_id in addition to the time in seconds and kept the WHERE conditions you provided

Hope that helps

SELECT  player_id, mapname, MIN(runtimepro) As shortest, 
MAX(runtimepro) As longest
FROM ck_playertimes times

WHERE

steamid = 'STEAM_1:0:20019070'
AND style = '0'
GROUP BY 
player_id, mapname

COUNT and GROUP BY min and max using INNER Join

2 Answers2