1. Registered User
Join Date
Sep 2003
Posts
13

Hi everyone!!!
I have a table Bid which has three fields we are interested in, iid (the item id), buyer (the buyer's id) and offer (the offer of that particular buyer for that paricular item). I need to find the name of the buyer who has the largest total value of bids. There is also a case where the same buyer might have bidded more than once for the same item. In that case we need to count only the highest bid... so we dont take into the sum both the bids, only the highest bid. This is what i have so far:
select max(tot_offer)
from (select sum(max_offer) as tot_offer
from (select max(offer) as max_offer, buyer, iid
from Bid
group by buyer, iid)

This query is correctly giving me the value of the highest total value of bids, but i need the buyer's id to whom this total corresponds to. The professor changed the query, it was more complicated and I reached so far due to some help from one of you guys. PLEASE HELP ME OUT.

Thank you

2. Registered User
Join Date
Sep 2003
Posts
13

Hey, I got the answer!!! In case someone is wondering how to solve this query, here it is. I hope who reads this isn't taking the same class I am, cause then we would have problems!!!

select uname from Users where id
from (select buyer, max(sum_offer) as highest_total
from (select buyer, sum(max_offer) as sum_offer
from (select buyer, max(offer) as max_offer
from Bid
group by buyer, iid)
order by highest_total desc)
where rownum = 1);

Cheers,

Originally posted by albdolph
Hi everyone!!!
I have a table Bid which has three fields we are interested in, iid (the item id), buyer (the buyer's id) and offer (the offer of that particular buyer for that paricular item). I need to find the name of the buyer who has the largest total value of bids. There is also a case where the same buyer might have bidded more than once for the same item. In that case we need to count only the highest bid... so we dont take into the sum both the bids, only the highest bid. This is what i have so far:
select max(tot_offer)
from (select sum(max_offer) as tot_offer
from (select max(offer) as max_offer, buyer, iid
from Bid
group by buyer, iid)