Lets see if I can explain this. What I am trying to do is..
SELECT ai.Product_ID, ai.Status, ai.reserve_price, ai.date_enddate,
MAX(DISTINCT ab.Bid_Amount) AS HighBid
FROM Auction_Items ai, Auction_Bids ab
WHERE ai.Status = 'ACTIVE'
AND ai.date_EndDate < #createODBCDate(now())#
AND ai.Product_ID = ab.Product_ID
AND ai.Reserve_Price <= ab.Bid_Amount
ORDER BY ab.Bid_Amount DESC
The second row starting with "MAX(" is giving me the problem. Without
the "MAX(" I can retrieve all of the bids that are tide to an auction
that has a status of ACTIVE, passed the End Date and are greater than
the Reserve Price, but what I want is only the highest bid that matches
that criteria. I would think that you could do this with the MAX
aggregate function, but I get a Missing Operator Error.
I have the two tables AUCTION_ITEMS and AUCTION_BIDS
At the end of each day, the system will check to see which Auctions are
still set to active, but past their end date. The system at the same
time should also check to see If these auctions have had any bids placed
on them and if so, has one of these bids reached and/or surpassed the
auction reserve price. If this has occurred, the system will return that
particular auction id and the highest bid amount.
The purpose for this is that the next thing the system will do is set
all of the ACTIVE auctions that are past their end date to CLOSED and
will submit a record to a third table AUCTION_SALES with the highest
auction bid * 1.5%.
The Two tables that I am working with are....
Table Name: Auction_Items
Fields: Product_ID [AutoNumber] KEY
Status [Text] (ACTIVE/CLOSED)
Reserve_Price [Currency]
Date_EndDate [Date/Time]
Table Name: Auction_Bids
Fields: Bid_ID [AutoNumber] KEY
Product_ID [Number] -This is the relation-
Member_ID [Number] -Not Important at this time-
Bid_Amount [Currency]
Thanks Everyone
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
