MySQL INNER JOIN to find the max value of group of rows

Question

I have put up a sample of what I'm trying to do here

http://sqlfiddle.com/#!2/514d2/2

This demo finds all rows in the "bids" table that are NOT the highest bid per listing_id. What I need to do though is find all the listings, where the $userid (in this case user 1) is the highest bidder.

Can someone help me figure this out?

For those that don't use SQLFiddle...

• Tablename: listings
• Tablename: bids

SQL:

SELECT listings.end_date, 
    listings.user_id, 
    listings.title, 
    listings.auc_fp, 
    listings.id, 
    listings.auc_image1 
FROM listings 
INNER JOIN 
    (SELECT 
     listing_id, 
     user_id, 
     bid maxBid 
 FROM bids 
 WHERE bid 
 NOT IN 
     (SELECT MAX(bid) 
      FROM bids) 
      GROUP BY 
         listing_id, 
         user_id) 
    bids ON 
      listings.id = bids.listing_id 
  WHERE bids.user_id=1 
  AND listings.end_date > NOW() 
  ORDER BY listings.list_ts DESC

This query successfully finds all the listings where the $userid has placed a bid, but is not the highest bidder. I'd like to find all the listings where the $userid has placed a bid and IS the highest bidder.

Answer 1

This should do what you need using a left join to ensure there's no better bid for each product;

SELECT DISTINCT listings.end_date, listings.user_id, listings.title, 
       listings.auc_fp, listings.id, listings.auc_image1
FROM listings 
JOIN bids b1 
  ON b1.listing_id=listings.id 
LEFT JOIN bids b2 
  ON b2.listing_id=listings.id AND b1.bid < b2.bid
WHERE b1.user_id = 1
  AND b2.bid IS NULL
  AND listings.end_date > NOW()

An SQLfiddle based on the original to test with.

Answer 2

Preliminary observation

The original query can be reformatted for easier readability to look like:

SELECT listings.end_date, 
       listings.user_id, 
       listings.title, 
       listings.auc_fp, 
       listings.id, 
       listings.auc_image1 
  FROM listings 
  JOIN (SELECT listing_id, 
               user_id, 
               bid maxBid 
          FROM bids 
         WHERE bid NOT IN (SELECT MAX(bid) FROM bids) 
         GROUP BY listing_id, user_id
       ) AS bids
    ON listings.id = bids.listing_id 
 WHERE bids.user_id = 1 
   AND listings.end_date > NOW() 
 ORDER BY listings.list_ts DESC

I note that the column aliassed maxBid is not necessarily the maximum bid for any listing. This query is also only for listings that are still open. I'm not convinced it produces the expected answer; the only bid excluded is the one which contains the overall highest bid for any listing whatsoever.

---

TDQD — Test-Driven Query Design

The real problem is:

Find all the currently open listings where the $userid (= 1) has placed a bid and is the highest bidder.

Time to do TDQD — Test-Driven Query Design; my brain is not working fast enough to do it all at once.

Maximum bid for each listing

SELECT listing_id, MAX(bid) AS maxBid
  FROM bids
 GROUP BY listing_id

Find the bids for a listing where the user who made the maximum bid is user 1

SELECT b.listing_id
  FROM bids AS b
  JOIN (SELECT listing_id, MAX(bid) AS maxBid
          FROM bids
         GROUP BY listing_id
       ) AS m
    ON m.listing_id = b.listing_id AND m.maxBid = b.bid
 WHERE b.user_id = 1

Find the listings where the user who made the maximum bid is user 1

SELECT l.*
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id

Limit the query to open listings

SELECT l.*
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id
 WHERE l.end_date > NOW()

You can and should replace the * with the exact columns you're interested in. If you need bid details, you can obtain those too.

---

Testing against Informix

There's a minor syntax change required — NOW() gets replaced by CURRENT YEAR TO SECOND — but that only affects the last query. There was somewhat more work required on the DDL to get the syntax into a more nearly DBMS-neutral format; MySQL has some odd conventions.

Note to future readers: replace the NOW() or CURRENT YEAR TO SECOND with a value such as 2012-12-29 12:37:43 to get the same results. The listings in the data expire from `2013-01-10 00:00:001 onwards.

Query 1:

SELECT listing_id, MAX(bid) AS maxBid
  FROM bids
 GROUP BY listing_id;

Output 1:

listing_id  maxbid
34          95.37
40          103.00
38          507.00
41          94.00
48          76.00
6469        22.00
6472        5.00
37          511.00
31          100.00

Query 2:

SELECT b.listing_id
  FROM bids AS b
  JOIN (SELECT listing_id, MAX(bid) AS maxBid
          FROM bids
         GROUP BY listing_id
       ) AS m
    ON m.listing_id = b.listing_id AND m.maxBid = b.bid
 WHERE b.user_id = 1;

Output 2:

listing_id
34
37
48
6469
6472

Query 3:

SELECT l.end_date, l.user_id, l.title, l.id
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id;

Output 3: end_date user_id title id 2013-01-09 08:11:16 1 Christmas Tree 6469 2013-01-11 09:17:31 3 Another test item 6472

Query 4

SELECT l.end_date, l.user_id, l.title, l.id
  FROM listings AS l
  JOIN (SELECT b.listing_id
          FROM bids AS b
          JOIN (SELECT listing_id, MAX(bid) AS maxBid
                  FROM bids
                 GROUP BY listing_id
               ) AS m
            ON m.listing_id = b.listing_id AND m.maxBid = b.bid
         WHERE b.user_id = 1
       ) AS u
    ON l.id = u.listing_id
 WHERE l.end_date > CURRENT YEAR TO SECOND;

Output 4:

end_date                user_id     title                   id
2013-01-09 08:11:16     1           Christmas Tree          6469
2013-01-11 09:17:31     3           Another test item       6472