Mysql – Filter and then sort products by availability

I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times.

products
- id_productSupplier (primary key)
- id_product (foreign key)
- availability (int - how many days takes to deliver product)


I need:

• list of 10 product_id
• 1 record per id_product with lowest availability
• sort it by availability of lowest available availability per product_id

For example, when id_productSupplier has availability=0, and other id_productSupplier with same product_id has availability=5, I want to order this product_id with availability=0.

Data:

id_productSupplier | id_product | availability
1 | 1 | 2
2 | 1 | 5
3 | 2 | 7
4 | 2 | 4
5 | 3 | 1


Expected result:

id_productSupplier | id_product | availability
5 | 3 | 1
1 | 1 | 2
4 | 2 | 4


Here what I have so far:

SELECT id_product FROM products GROUP BY id_product ORDER BY availability LIMIT 10


This does not really work, as it first randomly groups id_product and then orders by availability…

So, is there way to first get rid of records with greater availability for particular product_id and then sort it by availability?

I use MariaDB 5.5 (almost same as MySQL 5.5).

This is a "top n per group" problem but within the limits of MySQL.
You need to GROUP first then join back

SELECT
F.*
FROM
(
SELECT
MIN(availability) AS MINavailability, id_product
FROM
foo
GROUP BY
id_product
) X
JOIN
foo F ON X.id_product = F.id_product AND X.MINavailability = F.availability
ORDER BY
F.availability, F.id_product