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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With