23

Seemingly simple MySQL question, but I've never had to do this before..

I have two tables, items and prices, with a one-to-many relationship. 

Items Table
id, name

Prices Table
id, item_id, price

Where

prices.item_id = items.id

What I have so far:

SELECT items.id, items.name, MIN(prices.price)
FROM items
LEFT JOIN prices ON items.id = prices.item_id
GROUP BY items.id

How do I also return the corresponding prices.id for that minimum price? Thanks!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Charles
  • 853
  • 3
  • 8
  • 21

4 Answers4

36

This will return multiple records for a record in Items if there are multiple Prices records for it with the minimum price:

select items.id, items.name, prices.price, prices.id
from items
left join prices on (
    items.id = prices.item_id 
    and prices.price = (
        select min(price)
        from prices
        where item_id = items.id
    )
);
Patrick Finnegan
  • 501
  • 3
  • 4
  • Brilliant, thank you. I was getting closer to coming up with this... knew I would need a subquery somewhere. – Charles Sep 28 '11 at 19:26
  • This is the query I was working on to replace my answer, but Patrick beat me to it. – Sonny Sep 28 '11 at 19:33
  • 2
    How is this for efficiency as implemented a similar query and its killed my load time, even with various indexes etc – Horse Jan 11 '12 at 14:52
  • 2
    Seems to work, but I have the same problem like @Horse The load time is not acceptable and is not production ready in my case. – merlin Jan 09 '16 at 22:44
17

New, working answer, based on the final example in the MySQL 5.0 Reference Manual - 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column:

SELECT items.id, items.name, prices.price, prices.id
FROM items 
LEFT JOIN prices
    ON prices.item_id = items.id
LEFT JOIN prices AS filter
    ON filter.item_id = prices.item_id
    AND filter.price < prices.price
WHERE filter.id IS NULL

The LEFT JOIN works on the basis that when prices.price is at its minimum value, there is no filter.price with a smaller value and the filter rows values will be NULL.

Original incorrect answer:

SELECT items.id, items.name, prices.price, prices.id
FROM items 
LEFT JOIN prices ON prices.item_id = items.id
ORDER BY prices.price ASC
LIMIT 1
Sonny
  • 8,204
  • 7
  • 63
  • 134
  • 1
    I'm trying to display all results in the Items table. The LIMIT 1 here is applied to the main Items query and only returns one result. – Charles Sep 28 '11 at 19:18
0

Ok, how about?

SELECT items.id, items.name, MIN(prices.price), prices.id
FROM items 
LEFT JOIN prices ON items.id = prices.item_id 
GROUP BY items.id, MIN(prices.price)
JMK
  • 27,273
  • 52
  • 163
  • 280
  • 1
    When I do that, it returns the first prices.id that it finds, not the one that corresponds to the Minimum one. – Charles Sep 28 '11 at 19:04
0
SELECT top 1 items.id, items.name, prices.price, prices.id 
FROM items  
LEFT JOIN prices ON items.id = prices.item_id  
ORDER BY prices.price ASC
Gajus
  • 69,002
  • 70
  • 275
  • 438
Eric R.
  • 1,105
  • 3
  • 21
  • 48