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!

@AhmetKarabulut No, adding `LIMIT 1` would only work if the OP wanted just a single record from an entire query. It won't work if single records from many groups are wanted. — Tim Biegeleisen, Sep 05 '17 at 13:56
Not a job for `GROUP BY`. You cannot select rows using `GROUP BY`. `GROUP BY` computes new rows using the data from each group. Take a look at [this answer](https://stackoverflow.com/a/28090544/4265352) on a [similar question](https://stackoverflow.com/q/12102200/4265352). — axiac, Sep 05 '17 at 14:01

Tim Biegeleisen · Accepted Answer · 2017-09-05T17:33:00.583

5

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.

edited Sep 05 '17 at 17:33

answered Sep 05 '17 at 13:51

Tim Biegeleisen

502,043
27
286
360

Thanks for your answer! Is there a way to speed up the query? My query that I posted take about 30 seconds to run. Your statement runs now for 44 minutes, which would be no problem for me, because I just need to run it once a day. I will answer you as soon as I have the result. – Vidarrus Sep 05 '17 at 14:44
@Vidarrus I think you can add a composite index to speed up this query. I don't know how much faster it can get, but give my suggestion a try and let us know the result. – Tim Biegeleisen Sep 05 '17 at 14:53
I have now the result of your query. The index has accelerated it a lot. But your query does not give me the wished result, because it does not check the qty of a product. In my example Merch_B would be the chosen merchant for product 3217, but it should be Merch_A because Merch_A has the cheapest price WITH an qty > 0. If I would add "WHERE qty > '0'" to your statement it would work, but sometimes there are products where no merchant has any qty available, so I wanted to have the merchant with the best price to be choosen. – Vidarrus Sep 05 '17 at 16:54
@Vidarrus I updated my answer to handle this additional logic. The problem now is that the index may no longer speed up the query. If you really need a lightning fast query, you may have to restructure your data model. – Tim Biegeleisen Sep 05 '17 at 17:34
After indexing my columns new I'm able to run your query within a minute. The result is what I was looking for, thank you for your help! – Vidarrus Sep 06 '17 at 00:29

MySQL only show first row of specific field

1 Answers1

Rextester