ranking - mysql: group by ID, get highest priority per each ID

Question

I have the following mysql table called "pics", with the following fields and sample data:

id   vehicle_id    filename    priority
1    45            a.jpg       4
2    45            b.jpg       1
3    56            f.jpg       4
4    67            cc.jpg      4
5    45            kt.jpg      3
6    67            gg.jpg      1

Is it possible, in a single query, to get one row for each vehicle_id, and the row be the highest priority?

The result I'm looking for:

array (
  [0] => array( [id] => '2', [vehicle_id] => '45', [filename] => 'b.jpg',  [priority] => '1' ),
  [1] => array( [id] => '3', [vehicle_id] => '56', [filename] => 'f.jpg',  [priority] => '4' ),
  [2] => array( [id] => '6', [vehicle_id] => '67', [filename] => 'gg.jpg', [priority] => '1' )
);

If not possible in a single query, what would be the best approach?

Thanks!

Answer 1

This seems to be a typical max per group query. In most databases you could easily do this using ROW_NUMBER:

SELECT id, vehicle_id, filename, priority
FROM (
    SELECT 
        id, vehicle_id, filename, priority,
        ROW_NUMBER() OVER (PARTITION BY vehicle_id
                           ORDER BY priority DESC, id) AS rn
    FROM pics
) AS T1
WHERE rn = 1

Since MySQL doesn't yet support ROW_NUMBER you can emulate it using variables:

SELECT id, vehicle_id, filename, priority
FROM (
    SELECT
        id, vehicle_id, filename, priority,
        @rn := CASE WHEN @prev_vehicle_id = vehicle_id
                    THEN @rn + 1
                    ELSE 1
               END AS rn,
        @prev_vehicle_id := vehicle_id
    FROM (SELECT @prev_vehicle_id := NULL) vars, pics T1
    ORDER BY vehicle_id, priority DESC, id
) T2
WHERE rn = 1