Please Help with GROUP BY Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Please Help with GROUP BY Query

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-02-27 : 16:15:12

I have a bid sheet table which includes the "ItemID", "BidAmount" and "BidderID" among other fields. I'm trying to create a QUERY which returns only the winning bidders for each item. Using the following query, I can return the top bids for all items in the bid sheet:

SELECT ItemID, MAX(BidAmount) AS TopBid

FROM tblBidSheet

GROUP BY ItemID

However, I'm having difficulty obtaining the BidderID in this same query. Whether I include BidderID in a subquery or in the GROUP BY statement the query will then return data for all of the bidders; rather than just the ItemID, Top Bid and BidderID for each of the winning bids. Thanks in advance for any assistance on this!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 16:20:19

[code]

SELECT t2.ItemID, t2.BidAmount, t1.BidderID
FROM tblBidSheet t1
INNER JOIN ( SELECT ItemID, MAX(BidAmount) AS TopBid
FROM tblBidSheet
GROUP BY ItemID
) AS t2
ON t1.ItemID = t2.ItemID

[/code]

Tara

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-02-27 : 16:38:44

Thanks for the quick response. I've made the following modifications to your QUERY to avoid QA errors:

SELECT t2.ItemID, t2.TopBid, t1.BidderID
FROM tblBidSheet t1
INNER JOIN ( SELECT ItemID, MAX(BidAmount) AS TopBid
FROM tblBidSheet
GROUP BY ItemID
) AS t2
ON t1.ItemID = t2.ItemID

ORDER BY t2.ItemID

However, this query still returns a row for each ItemID rather than just one row for each winning bid.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 16:41:41

I'm not understanding then. Could you post some sample data and what the expected result set would be using that sample data? From there, it should be easy to fix the query.

Tara

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-02-27 : 16:44:57

I got it to work by also including the BidAmounts in the INNER JOIN criteria. Thanks!

SELECT t2.ItemID, t2.TopBid, t1.BidderID
FROM tblBidSheet t1
INNER JOIN ( SELECT ItemID, MAX(BidAmount) AS TopBid
FROM tblBidSheet
GROUP BY ItemID
) AS t2
ON t1.ItemID = t2.ItemID AND t1.BidAmount = t2.TopBid

ORDER BY t2.ItemID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 16:45:50

Ah, yes I forgot to include those. Sorry about that. Glad you figured it out.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources