MySQL only show first row of specific field

Question

The following query:

SELECT *
    FROM productlist.pricelist_merchant
    WHERE product_id <> '0'
ORDER BY 
    product_id ASC, 
    qty = 0, 
    price ASC;

Returns:

Merchant|product_id|price |qty|
Merch_A |3217      |44.30 |16 |
Merch_Z |3217      |45.14 |2  |
Merch_U |3217      |45.62 |16 |
Merch_I |3217      |46.06 |16 |
Merch_Q |3217      |48.98 |55 |
Merch_B |3217      |39.58 |0  |
Merch_T |3217      |45.97 |0  |
Merch_M |3217      |46.40 |0  |
Merch_L |3220      |105.84|1  |
Merch_Z |3220      |147.00|3  |
Merch_U |3220      |149.36|2  |
Merch_A |3220      |149.99|2  |
Merch_Q |3220      |153.53|90 |
Merch_I |3220      |154.51|2  |
Merch_T |3220      |157.41|4  |
Merch_C |3220      |164.59|46 |
Merch_M |3220      |136.10|0  |
Merch_B |3220      |136.71|0  |

Which is perfect for me. The product_id is sorted by price if there is qty available. But I need only the first row of a product_id. I would like to have this:

Merchant|product_id|price |qty|
Merch_A |3217      |44.30 |16 |
Merch_L |3220      |105.84|1  |

Only the first row of a product_id interests me. Is there a way to change my query to achieve this? I tried a lot of statements and none of it worked...

Thanks in advance!

Answer 1

One canonical (and ANSI-compliant) way of handling this query is to just join your original table to a subquery which finds the records corresponding to the minimum prices for each product. Then, just do a SELECT * on your original table to get all information for these matching rows.

SELECT t1.*
FROM pricelist_merchant t1
INNER JOIN
(
    SELECT
        product_id,
        MIN(CASE WHEN qty > 0 THEN price END) AS min_price_qty,
        MIN(CASE WHEN qty = 0 THEN price END) AS min_price_no_qty
    FROM pricelist_merchant
    WHERE product_id <> '0'
    GROUP BY product_id
) t2
    ON t1.product_id = t2.product_id AND
       t1.price      = COALESCE(t2.min_price_qty, t2.min_price_no_qty)
ORDER BY t1.product_id

The trick here is to compute two minimum prices while aggregating by product. The first is the minimum price for records with a non zero quantity. This value will be used in the join unless no such records exist, in which case the query falls back to the minimum price for zero quantity records.

Output:

Demo here:

Rextester

It might not be possible to speed up the join between t1 (your original table) and t2, the derived table, in a significant way. However, we can certainly try to speed up the subquery which finds minimum price per product. Add a composite index on product_id and price:

CREATE INDEX idx ON pricelist_merchant (accountid, logindate);

Now the aggregation in the subquery should perform faster, and therefore the overall query might improve.