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

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.