mysql按id分组,mysql：按ID分组，每个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!

解决方案

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.