Mysql – Filter and then sort products by availability

group bymariadbMySQLorder-by

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).

Best Answer

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