Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: SQL Query Help !!!

    Here is the scenario:

    TableName: BIDS
    Fields:Lot_id, Bidder_id, Amount_bid

    I'm trying to make a query that will take the highest bid amount for each lot number.

    I did just a regular sort query that sorted by Lot_id(acsending) then Amount_bid(Decsending) and that was OK till I was told there's going to be around 1500 LOTS. Does anyone have any idea how to make a query that would fit this problem?

    Thanks,
    David Pino

  2. #2
    Join Date
    Oct 2003
    Posts
    2
    I think I'm one step closer to the answer. I'm trying to use a query just for two fields Lot_ID , Amount_bid and use the MAX statement for the Amount_bid field. Does anyone know how to make the MAX statement work in a query?

    Thanks,
    David Pino

  3. #3
    Join Date
    Oct 2003
    Posts
    37

    Re: SQL Query Help !!!

    Hi Dave

    A simple solution would probably be something like this (you didn't say what sort of sql environment you were working in) :-

    Select b.lot_id, b.bidder_id, b.amount_bid
    From bids b
    where b.amount_bid = (select max(amount_bid)
    from bids b2
    where b.lot_id = b2.lot_id)

    The only problem with this query is that if you have two bids of the same amount both rows would be returned - but you could get round that by removing the bidder_id column and grouping.

    Regards

    Keith

Posting Permissions

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