0

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

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.