CODEWITHSUNDEEP
mysql
Steven
Steven

Reputation: 13975

MySQL Join with Highest?

I have two tables, one called items and one called bids. Bids contains a bunch of bids with an item_id. I am trying to get all the information on the item with the associated highest bid.

I tried doing something like

SELECT * FROM items JOIN bids ON items.id=bids.item_id GROUP BY item_id

However that seems to return the first bid, not the highest.

How could I get the highest?

Upvotes: 0

Views: 130

Answers (2)

Sam Wilson
Sam Wilson

Reputation: 4512

A self-join will also do it, I think. So, assuming (as @eggyal did) that there's a bids.value column:

SELECT *
FROM items i
  JOIN bids b1 ON b1.item_id=i.id
  JOIN bids b2 ON b2.item_id=i.id AND b2.value>b1.value
WHERE b2.id IS NULL

But that will return multiple rows if there are mutiple bids for the same amount. How do you want to handle them?

(And that SELECT * should be narrowed down.)

Upvotes: 0

eggyal
eggyal

Reputation: 125845

You need to use a subquery to discover the maximum bid value, then join that with your existing query to obtain all of the desired output data. Assuming that the bid value is in a column called value, and that the PK of the bids table is a column called id:

SELECT items.*, bids.*
FROM
  items
  JOIN (
    SELECT id, item_id, MAX(value) AS value FROM bids GROUP BY item_id
  ) AS maxbids ON items.id = maxbids.item_id
  JOIN bids    ON  bids.id = maxbids.id
GROUP BY items.id

If there are multiple bids of the maximum amount, this query will return all of them.

Upvotes: 1

Related Questions