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

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

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