Joining 3 tables while using MAX Overview

I am attempting to join 3 tables auction, bids, and users. I need several key pieces of information from each table based off the bids table. In the end I want a summary overview of the auctions. To see the latest bid for each auction and the user that placed it.

I am used to doing simple SQL queries and I am not sure what I need to do in order to accomplish this. Not looking for a solution (although I would take it with an explanation) but learning experience.



Table Structures

auctions

id, title, location

bids

id, auction_id, user_id, bid_amount

users

id, name, address



Query So Far

So far this is what I have started with, any guidance on what to do from here is greatly appreciated.

SELECT 
    MAX(bids.id) AS 'Bid ID',
    bids.auction_id AS 'Auction ID',
    auctions.title AS 'Auction Title',
    MAX(bids.bid_amount) AS 'Bid Amount'
FROM
    boardman_bidding.bids
        INNER JOIN
    boardman_bidding.auctions ON bids.auction_id = auctions.id
        INNER JOIN
    boardman_bidding.users ON bids.user_id = users.id
WHERE
    bids.auction_id &gt; 1065 AND
    auctions.city_id = '45621'
GROUP BY bids.auction_id
ORDER BY bids.auction_id DESC;




Expected Output

I am hoping for the following:

bid_id, auction.title, auction.location, users.user_id, users.name, users.location, bid_amount
Bids should be two tables:


BidHistory -- containing details about each bid
CurrentBid -- Either some details (redundant) about the latest bid, or where all the details live until moved to the other table.


This adds complexity to the "make a bid" code.  But it simplifies the code that needs to know what the current bid is.  And it avoids the complex code to fetch the latest bid.

The Strawman:

There is a serious flaw in the query that you presented.  MAX(bid.id) and MAX(bids.bid_amount) do not necessarily refer to the same row.  (OK, perhaps they do in a bidding situation, but you should not assume it!)  This is where you need "Groupwise Max" solutions.  See the [greatest-n-per-group] tag.  There are a lot of discussions here and on stackoverflow.com about how to find the row that contains the max (or min) value of a particular column.  Some solutions involve a subquery.  Some solutions are slower than others.

See also http://mysql.rjweb.org/doc.php/groupwise_max