Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Unanswered: Please HELP - Simple query

    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)
    group by buyer);

    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. #2
    Join Date
    Sep 2003
    Posts
    13

    Re: Please HELP - Simple query

    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
    in (select buyer
    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)
    group by buyer)
    group by buyer
    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)
    group by buyer);

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •