March 15, 2008 at 9:33 am
I'm running into a wall when trying to determine the winner of an auction within a specific period of time (ie quarterly), where serveral items are up for bid and there may or may not be more than one bidder. I was thinking that selecting the max bidamount would do the trick but that returns just one bidder where I need the winning bidder for each of the items. Every bidder has a unique identifier. Here is the query that I'm having trouble with. I'm also getting the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference" during the process of manipulating this query. Any help or suggestions on how this might be done more effectively is appreciated.
select * from bidders where
BidAmount = (select Max(BidAmount) from bidders where (PTID in (Select PTID from Bidders where PTID IN (SELECT PTID FROM SaleInformation WHERE DateSold BETWEEN CONVERT(DATETIME, '7/1/2007', 102)AND CONVERT(DATETIME, '9/30/2007', 102)))))) and (PTID in (Select PTID from Bidders where PTID IN (SELECT PTID FROM SaleInformation WHERE DateSold BETWEEN CONVERT(DATETIME, '7/1/2007', 102)AND CONVERT(DATETIME, '9/30/2007', 102))))
March 15, 2008 at 10:06 am
Here are a couple of options I think will work, but I can't guarantee anything without seeing the table schemas and some sample data.
/*this uses a CTE and is SQL2005 and later compliant. It is easier to
read than the second solution*/
-- CTE will get the max bid for each ptid
With MaxBidByPtId As
(
Select
B.PTID
Max(B.BidAmount) as MaxBid
From
bidders B Join
SaleInformation SI On
B.PTID = SI.PTID
Where
SA.DateSold Between '7/1/2007' abd '9/30/3007'
Group By
B.PTID
)
Select -- return all bidder information that matches the max bid
B.*
From
bidders B Join
MaxBidByPtId MB On
B.PTID = MB.PTID
Or
-- this uses a derived table and will work in any version of SQL Server
Select -- select returns the bidder information
B.*
From
bidders B Join
-- derived table gets the max bid for each ptid in date range
(
Select
B.PTID
Max(B.BidAmount) as MaxBid
From
bidders B Join
SaleInformation SI On
B.PTID = SI.PTID
Where
SA.DateSold Between '7/1/2007' abd '9/30/3007'
Group By
B.PTID
) MB On
B.PTID = MB.PTID
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2008 at 10:18 am
Hey, thanks Jack, I'll give these a try and let you know. I really appreciate the input.
Brian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply