J-2X J-2X - 2 months ago 11
MySQL Question

mySQL - select the top n rows, grouped by 2 columns

I've found existing questions with the latest/lowest single row over several fields, or latest n rows over a single field, but not the two together

I need to construct 2 queries, using this example table (similar to this question)

CREATE TABLE 
lap_data
(
id
int(1) NOT NULL,
track_id
int(1) NOT NULL,
user_id
int(1) NOT NULL,
lap_time
time NOT NULL,
lap_status
tinyint(1) NOT NULL,
PRIMARY KEY (
id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `lap_data` (`id`, `track_id`, `user_id`, `lap_time`, `lap_status`) VALUES
(1, 1, 1, '04:18:23', 1),
(2, 2, 2, '01:09:54', 1),
(3, 2, 1, '01:05:30', 1),
(4, 1, 2, '04:17:02', 1),
(5, 3, 2, '01:13:10', 1),
(6, 4, 1, '01:36:59', 0),
(7, 3, 2, '01:18:10', 1),
(8, 2, 3, '01:06:42', 1),
(9, 1, 1, '04:16:12', 1),
(10, 1, 2, '04:18:12', 1),
(11, 2, 3, '01:03:20', 1),
(12, 2, 1, '01:08:13', 1),
(13, 2, 1, '01:09:44', 1),
(14, 3, 2, '01:14:10', 1),
(15, 3, 2, '01:17:20', 1),
(16, 4, 1, '01:36:23', 1),
(17, 2, 1, '01:11:34', 1);


Query 1:


  • I want the top 2 results by lap_time - per track, per user


    • so if a given user_id has 20 records spread over 3 tracks...


      • I would expect 6 results for that user_id

      • or 5 if one of those tracks only has a single record


    • the same lap time at 2 different tracks should both be displayed, not appear just once


  • I want to use a WHERE user_id IN (..) for part 1

  • ordered by a given field, ie lap_time

  • where lap_status = 1



This is the query I've been using elsewhere to collect the two latest records of a given type, which is the closest to what I think I need, modified to this example .. although unfinished, as in the other instance, it was only collecting the 2 most recent rows for each user_id .. here I need it to take track_id into account as well

SELECT
*
FROM (
SELECT
t.id,
t.track_id,
t.user_id,
t.lap_time,
t.lap_status,
@row:=case WHEN @prev=user_id THEN @row ELSE 0 END +1 rn,
@prev:=user_id
FROM `lap_data` t
CROSS JOIN (SELECT @row:=0, @prev:=null) c
WHERE t.user_id IN (1,2)
ORDER BY user_id, id DESC
) src
WHERE rn <= 2
ORDER BY lap_time DESC


Query 1 Desired result: ( WHERE user_id IN (1,2) )


| id | track_id | user_id | lap_time | lap_status |
--------------------------------------------------------------
| 9 | 1 | 1 | 04:16:12 | 1 |
| 1 | 1 | 1 | 04:18:23 | 1 |
| 3 | 2 | 1 | 01:05:30 | 1 |
| 12 | 2 | 1 | 01:08:13 | 1 |
| 16 | 4 | 1 | 01:36:23 | 1 |
| 6 | 4 | 1 | 01:36:59 | 1 |
| 4 | 1 | 2 | 04:17:02 | 1 |
| 10 | 1 | 2 | 04:18:12 | 1 |
| 2 | 2 | 2 | 01:09:54 | 1 |
| 5 | 3 | 2 | 01:13:10 | 1 |
| 14 | 3 | 2 | 01:14:10 | 1 |


Query 2:


  • I want to get the inverse of the above, for a single user


    • ie the remainder of the results excluding the first two, for just a specific user_id




Query 2 Desired result: ( WHERE user_id = '1' )


| id | track_id | user_id | lap_time | lap_status |
--------------------------------------------------------------
| 13 | 2 | 1 | 01:09:44 | 1 |
| 17 | 2 | 1 | 01:11:34 | 1 |


Any suggestions? thanks!

Answer

MYSQL Version for my previous answer:

SELECT 
lt.id,
lt.user_id,
lt.track_id,
lt.lap_status,
lt.lap_time 
FROM (
SELECT id,
    user_id,
    track_id,
    lap_status,
    lap_time,
    CASE WHEN track_id = @prevTrackId AND user_id = @prevUserId THEN @curRank := @curRank + 1 ELSE @curRank := 1 END Rank,
    @prevTrackId := track_id,
    @prevUserId := user_id
FROM lap_data, (SELECT @curRank := 0, @prevTrackId := 1, @prevUserId := 1) r
ORDER BY user_id,track_id,lap_time ASC
) lt 
WHERE Rank <= 2