MySQL group rows

Question

I have a table with:
Id (id of rider)
Time (is the time that he has done)
Session (number session where the time was done)

idlap   idRider session time
1        45652    1      4
2        54645    1      2
3        45652    2      2
4        54582    2      1
5        51284    1      3
6        54582    1      3
7        54645    2      4
8        51284    2      5
9        54582    2      2

I wonder how to query in MySQL a result like:

idRider   |fast sesion   |  count laps
45652     |     2        |     1
54645     |     1        |     1
51284     |     3        |     1
54582     |     2        |     2

The query should return in which session the rider got the best time(fast session) and count the number of laps (done in the fast session) grouped by idRider. Thank you.

Answer 1

This query will give you the results that you want. It JOINs the laps table to the minimum time and number of laps per rider per session, and then to the minimum time per rider to get the fastest session:

SELECT l.idRider, l.session, l.time, s.numlaps
FROM laps l
JOIN (SELECT idRider, `session`, MIN(time) AS fastest, COUNT(*) AS numlaps
      FROM laps
      GROUP BY idRider, `session`) s ON s.idRider = l.idRider AND s.session = l.session AND s.fastest = l.time
JOIN (SELECT idRider, MIN(time) AS fastest
      FROM laps
      GROUP BY idRider) f ON f.idRider = s.idRider AND f.fastest = s.fastest
ORDER BY idRider

Output:

idRider session time    numlaps
45652   2       2       1
51284   1       3       1
54582   2       1       2
54645   1       2       1

Demo on dbfiddle