Seeing as how the list has been "pretty inactive" the past couple weeks, I thought I'd throw in a SQL, <@filter>, <@for> question. I would like one row per expired auction with the highest bid including the buyer and seller info. I would like to accomplish this with SQL, if possible, as I know Robert has pointed out that Witango is a little slow on the <@for> iterations. The tables are as follows:
Auction (a1) - Auction info Bid (b3) - Bid info User - Seller (u2) and Buyer (u4) info I have the following SQL SELECT a1.AuctionID, a1.AuctionStartingBid, a1.AuctionReservePrice, b3.BidAmount, MAX(b3.BidAmount) AS AuctionMaxBid, (a1.AuctionStartTimeStamp + (a1.AuctionDuration * 86400) - 1253801128) as RemainingTime, u2.U_ID AS SellerID, u2.U_FirstName AS SellerFirstName, u4.U_ID AS BuyerID, u4.U_FirstName AS BuyerFirstName, FROM users u4, users u2, auction a1 LEFT OUTER JOIN Bid b3 ON a1.AuctionID = b3.AuctionID WHERE ( (b3.U_ID = u4.U_ID) AND (a1.U_ID = u2.U_ID) AND ((a1.AuctionStartTimeStamp + (a1.AuctionDuration * 86400) - 1253801128) <= '0') ) GROUP BY a1.AuctionID HAVING ( (AuctionMaxBid >= a1.AuctionReservePrice) AND (AuctionMaxBid >= a1.AuctionStartingBid) ) ORDER BY a1.AuctionID ASC, b3.BidAmount DESC The problem I am running into is that the row for each auction displays everything correctly except the Buyer (u4) information. It seems that the "Group By" clause reduces the rows down to one per auction, but it selects the Buyer info from the first bid instead of the last bid. If the "Order By" clause executed before the "Group By" clause then it would work correctly, but I get a syntax error if I move it before the "Group By" clause. Is there anything I can do to accomplish my goal with this SQL or am I going to have to remove the MAX and GROUP BY clauses, thereby selecting a row for each bid and just use the Witango <@for> tags to iterate through the result and select the maximum bid per auction? I couldn't figure out a way to use the <@filter> tag either. Thanks, Steve Fogelson Internet Commerce Solutions "In God We Trust" ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
