>
> I have an auction site that I am working on. I have two tables,
> Auction_Item table and Auction_Bid table.
>
> The item table houses the product id, auction close date, the
> reserve price and the seller id. The bid table houses the
> product_id, bid amount and bidder id.
>
> What I want to do is show a list of auctions that have reached their
> close date and have a bid exceeding the reserve price.
>
> I can do this. The trickey part is, I dont want to see all of the
> bids exceeding the reserve. Only the highest bid.
>
> here is what I have so far....
>
<cfquery name="Auction_Sale" datasource="#DatasourceName#"
dbtype="ODBC">
SELECT
MAX(ab.Bid_Amount as HighBid),
ab.Member_ID as Bidder,
ab.product_id,
ai.reserve_price,
ai.date_enddate as ENDDATE,
ai.product_identifier,
ai.member_ID as SELLER,
ml.userid as BIDDER_NAME
FROM
Auction_Bids ab,
Auction_Items ai,
member_list ml
WHERE ai.status = 'active'
AND ai.date_enddate < CURRENT_TIMESTAMP
AND ab.bid_amount >= ai.reserve_price
AND ai.product_ID = ab.Product_ID
AND ab.Member_ID = ml.member_ID
GROUP BY
ab.Member_ID,
ab.product_id,
ai.reserve_price,
ai.date_enddate,
ai.product_identifier,
ai.member_ID,
ml.userid
ORDER BY
ai.date_enddate,
ai.product_ID,
ab.bid_amount DESC
</cfquery>
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
