CODEWITHSUNDEEP
mysqlsqlmysql-workbench
makl1801
makl1801

Reputation: 21

How two join to tables based on the highest value in one of them?

I have a table named Auction which holds an auctionID. I have another table named bid which holds the auctionID (as a foreign key) and what the amount was.

What I want to do is list the auctionID in one column and the amount of the highest bid in the other column.

I have tried different ways of doing inner joins to get this to display properly but failed horribly. Please keep in mind that I'm a total novice when it comes to MYSQL and the solution may very well be that I should go back and redesign the whole database.

The closest I got to display what I want is the two different methods below.

First one

SELECT auction.auctionID, bid.bid
from bid
INNER JOIN 
auction on auction.auctionsID = bid.auctionsID;

The second one is as follows

SELECT auction.auctionsID, bid.bid 
from bid
INNER JOIN 
auction on auction.auctionsID = bid.auctionsID
where bid.bid = (select max(bid.bid) from bid);

The first one displays all bids that have been made and what amount they where, but i only want the highest bid on each auctionID.

The second one displays only the absolute highest bid ever made on all different auctions and what auctionID that specific auction had.

My guess is that i need some sort of nested join statements but I really cant wrap my head around it.

Thanks in advance!

Edit: What a great experience. As a long time user that out of desperation created an account today to ask my first question. I got awesome help from this community.

The answer:

SELECT auctionid, MAX(bid) as maxbid FROM bid GROUP BY auctionid;

Provided by Caius Jard and Deependra Bhandari

Upvotes: 1

Views: 67

Answers (4)

Caius Jard
Caius Jard

Reputation: 74605

Group the bids table before you join

SELECT * FROM
  auction a
  INNER JOIN
  (SELECT auctionid, MAX(bid) as maxbid FROM bid GROUP BY auctionid) b
  on a.auctionid = b.auctionid

You'll hence get the details of each auction (including id) and just the maximum bid for each auction

Deependra made a good point; you did say you only really wanted the auctionid and the max bid and this info can be derived solely from the bids table (the subquery aliased as b in my code above so if it's truly all you need, strip my query down to just the subquery. If you later want all the other info about an auction, along with its max bid, use the query above

If you want all the auction info and all the maxbid info, for each auction, you can do it thus:

SELECT * FROM
  auction a
  INNER JOIN
  bid b 
  ON a.auctionid = b.auctionid
  INNER JOIN
  (SELECT auctionid, MAX(bid) as maxbid FROM bid GROUP BY auctionid) mb
  on a.auctionid = mb.auctionid and b.bid = mb.maxbid

This will return all the info about an auction and all the info about the max bid. Hopefully you only allow unique bids, otherwise we're going to get into a more complicated "latest" max bid scenario

Upvotes: 0

GMB
GMB

Reputation: 222432

If you want to pull out the bid of the bid having the highest amount for a given auction, use a JOIN with a correlated subquery :

SELECT a.auctionsID, b.bid 
FROM 
    bid
    INNER JOIN auction a
        ON  a.auctionsID = b.auctionsID
        AND b.amount = (SELECT MAX(amount) FROM bid WHERE bid = b.bid)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I think you just want a GROUP BY query:

SELECT
    a.auctionID, MAX(b.bid) AS max_bid
FROM auction a
INNER JOIN bid b
    ON a.auctionsID = b.auctionsID
GROUP BY
    a.auctionID;

This assumes that you want to find the highest bid for each auction.

Upvotes: 1

Deependra Bhandari
Deependra Bhandari

Reputation: 51

If you need only two columns and both are available in bid table why you are making join. Use the following one.

SELECT auctionID, max(bid) as Highest_Invoice from bid group by auctionID;

Upvotes: 0

Related Questions