Get other columns associated with MAX column

Question

I have the following data:

BIDS

record_id    listing_id  user_id  amount   proxy
------------------------------------------------
43           26          3        75000    0
44           26          29       79100    1
45           26          29       75100    0

LISTINGS

record_id    reserve_price   start_price   user_id   status
-----------------------------------------------------------
26           80000           75000         2         0

I want to return the row associated with the maximum non-proxy bid amount, i.e. where proxy=0

My query is as follows, but it does not return the correct user_id. The maximum non-proxy bid is $75100, but it returns user_id=3, which is incorrect.

select 
        IFNULL(MAX(b.amount),0) AS maxBid
        , IFNULL(l.reserve_price,0) as reserve_price
        , IFNULL(l.start_price,0) as start_price
        , b.user_id 
    from bids b 
     join listings l on l.record_id = b.listing_id 
     where b.listing_id = 26 
     and l.status = 0 
     and b.proxy <> 1 
     order by maxBid desc, high_bidder desc limit 1

I've looked at other solutions to similar problems, but can't get anything to work so far.

@user460114 check https://stackoverflow.com/a/52358021/2469308 — Madhur Bhaiya, Sep 16 '18 at 20:17
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Sep 16 '18 at 21:30

Madhur Bhaiya · Accepted Answer · 2018-09-16T20:18:44.810

In MySQL versions < 8.0 (lack of window functions), you can try the following:

SELECT 
    IFNULL(MAX(b.amount),0) AS maxBid
    , IFNULL(l.reserve_price,0) as reserve_price
    , IFNULL(l.start_price,0) as start_price
    , SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT b.user_id   
                                   ORDER BY b.amount DESC  
                                   SEPARATOR ','), 
                      ',', 
                      1) AS user_id 
FROM bids b 
JOIN listings l on l.record_id = b.listing_id 
WHERE b.listing_id = 26 
 and l.status = 0 
 and b.proxy <> 1 
GROUP BY b.listing_id

Interesting use of SUBSTRING_INDEX with GROUP_CONCAT is explained here.

score 0 · Answer 2 · answered Sep 16 '18 at 20:12

I think you just need to order the results properly. You are not ordering your result by amount. -

select 
        IFNULL(MAX(b.amount),0) AS maxBid
        , IFNULL(l.reserve_price,0) as reserve_price
        , IFNULL(l.start_price,0) as start_price
        , b.user_id 
    from bids b 
     join listings l on l.record_id = b.listing_id 
     where b.listing_id = 26 
     and l.status = 0 
     and b.proxy <> 1 
     order by b.amount desc, maxBid desc, high_bidder desc limit 1

Get other columns associated with MAX column

2 Answers2