Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 872
briang Avatar asked Dec 08 '22 02:12

briang


1 Answers

While this may be the 'accepted' answer, the performance of Mark's solution is under normal circumstances many times better, and equally valid for the question, so by all means, go for his solution in production!


SELECT a.id, a.vehicle_id, a.filename, a.priority
FROM pics a
LEFT JOIN pics b               -- JOIN for priority
ON b.vehicle_id = a.vehicle_id 
AND b.priority > a.priority
LEFT JOIN pics c               -- JOIN for priority ties
ON c.vehicle_id = a.vehicle_id 
AND c.priority = a.priority 
AND c.id < a.id
WHERE b.id IS NULL AND c.id IS NULL

Assuming 'id' is a non-nullable column.

[edit]: my bad, need second join, cannot do it with just one.

like image 84
Wrikken Avatar answered Dec 10 '22 13:12

Wrikken