Select highest bid in table with multiple rows

Question

I'm working with a bids table for a marketplace and am trying to get the highest bids of a user. A user is able to bid multiple times (with increasing amounts), therefore I only want the highest bid for each listing item.

+----+------------+---------+--------+
| id | listing_id | user_id | amount |
+----+------------+---------+--------+
|  1 |          1 |       1 |      5 |
|  2 |          2 |       1 |     10 |
|  3 |          2 |       1 |     15 |
+----+------------+---------+--------+

I'd like the end result to be:

+----+------------+---------+--------+
| id | listing_id | user_id | amount |
+----+------------+---------+--------+
|  1 |          1 |       1 |      5 |
|  3 |          2 |       1 |     15 |
+----+------------+---------+--------+

I've tried the following query:

select bids.id, bids.listing_id, bids.user_id, max(bids.amount) as amount 
from bids 
group by listing_id;

But this gives me the following (to me) strange result:

+----+------------+---------+--------+
| id | listing_id | user_id | amount |
+----+------------+---------+--------+
|  1 |          1 |       1 |      5 |
|  2 |          2 |       1 |     15 |
+----+------------+---------+--------+

Amount 15 is correct, but ID=2 is from the record with amount=10. How do I get the correct result with record id=1 and id=3?

Answer 1

To solve this top 1 per group problem, you want to filter rather than aggregate.

Here is one way to do it with a correlated subquery:

select b.*
from bids b
where b.amount = (
    select max(b1.amount) 
    from bids 
    where b1.listing_id = b.listing_id and b1.user_id = b.user_id
)

Another typical solution is to use a anti-left join:

select b.*
from bids b
left join bids b1 
    on b1.listing_id = b.listing_id and b1.user_id = b.user_id and b1.amount > b.amount
where b1.id is null

Answer 2

You can use correlated sub-query :

select b.*
from bids b
where b.amount = (select max(b1.amount) from bids b1 where b1.listing_id = b.listing_id );

Other option is to use ranking function :

select b.*
from (select b.*, rank() over (partition by b.listing_id order by b.amount desc) as seq
      from bids b
     ) b
where seq = 1;

Answer 3

You're running your SQL without ONLY_FULL_GROUP_BY mode on, which means MySQL will just pick any value it likes for the columns that aren't grouped and aren't aggregated. This is what's effectively being run:

select ANY(bids.id), bids.listing_id, ANY(bids.user_id), max(bids.amount) as amount
from bids group by listing_id

ANY isn't a real aggregate function, but imagine it is and it just returns a random result from the group. This is how your data becomes mixed up

You can run the group as a subquery and join the group back to the table to get the rest of the data, like this:

select * from
(
  select listing_id, max(bids.amount) as amount
  from bids 
  group by listing_id
) findmax
inner join 
bids b
on 
  b.listing_id = findmax.listing_id and 
  b.amount = findmax.maxamount

This will return multiple rows per listing ID if two users bid the same amount

If you're on MySQL 8 you can use a window function to find the top amount:

select * from
(
  select 
    bids.id, bids.listing_id, bids.user_id, bids.amount as amount,
    row_number() over(partition by bids.listing_id order by amount desc) as rown
  from bids group by listing_id, bids.id, bids.user_id
) x
where x.rown = 1

I recommend you enable "only full group by"

Answer 4

You can use below format and get exact output:

select  B.id, B.listing_id, B.user_id, id, A.max_amount
from bids B
left outer join (
  select listing_id, user_id, max(amount) as max_amount from bids group by listing_id, user_id
) as A on A.listing_id = B.listing_id and A.user_id = B.user_id
order by B.listing_id, B.user_id, B.id; 

Answer 5

You can try this below:

 select b.id,b.listing_id,b.user_id,b1.amount from bids b inner join (select listing_id,max(amount) as amount from bids group by listing_id) b1
    on b.amount=b1.amount