3

In a table that has the columns: 'product_id', 'price', ... (and others). I need to obtain the record with the lowest and unique price for a given product_id.
I tried different sentences without having the expected result. Finally I came across this sentence (for product_id = 2):

SELECT `price` , `product_id`
FROM bids
WHERE `product_id` = 2
GROUP BY `price`
HAVING COUNT( `price` ) = 1
ORDER BY `price`
LIMIT 1;

But it seems not to be an elegant solution having recourse to LIMIT and ORDER. How can I obtain the same record using the MIN() function or even something else?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Enric
  • 33
  • 4

2 Answers2

1

This should work because you are already specifying the product_id to analyze:

SELECT MIN(t1.price) AS price, t1.product_id
FROM
(
    SELECT price, product_id
    FROM bids
    WHERE product_id = 1
    GROUP BY price, product_id
    HAVING COUNT(price) = 1
) t1

Notes: MIN/MAX vs ORDER BY and LIMIT

Community
  • 1
  • 1
Nonym
  • 6,199
  • 1
  • 25
  • 21
  • This works! Do you think that using this sentence rather than mine would improve the efficiency of the MySQL call? Even if not, it seems to be a neater solution. – Enric Dec 29 '11 at 18:39
  • @Enric, I'd like to refer you to an old post! See here: http://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit I've also modified my answer to include it.. – Nonym Dec 29 '11 at 18:46
  • Thank you very much. Your answer was helpful, up to now is what I'm going to use. – Enric Dec 29 '11 at 18:54
0

Skydreamer, I'm not sure but as I understand it the op wants the the first unique value. If the prices are 1,1,2,2,3 the query should return the row with the price of 3.

MTönnberg
  • 19
  • 3