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.
| Author |
Topic |
|
dlouche
Starting Member
9 Posts |
 Posted - 2009-03-14 : 18:21:25
|
| I have a table that tracks bids on an auction:UserID | AuctionID | BidAmount------------------------------------1 1 2.002 1 3.003 1 4.001 2 2.002 2 3.003 3 2.001 3 3.00 and I would like to get the auctionID's for a UserID if they have the highest bid amount.The way I am doing it now if pretty inefficient.Any help would be appreciated!Thanks in advance!Dan |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-14 : 18:54:30
|
| create table #auction(UserId int,AuctionId int, BidAmount int)insert into #auction(UserId,AuctionId,BidAmount) values(1,1,2.00),(2,1,3.00),(3,1,4.00),(1,2,2.00),(2,2,3.00),(3,3,2.00),(1,3,3.00)select a.* from #auction a inner join(select auctionid,MAX(bidAmount)maxBid from #auction Group By AuctionId)maxAmt ON a.AuctionId=maxAmt.AuctionId AND a.BidAmount=maxAmt.maxBidMike"oh, that monkey is going to pay" |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-14 : 21:58:33
|
| [code]Select UserID,AuctionID,BidAmountfrom(Select ROW_NUMBER() OVER(PARTITION BY AuctionID Order by BidAmount desc)as ROWID,* from YOURTABLE)ZWhere Z.ROWID =1[/code] |
|
|
|
|
|
|
|
|
Records with the highest value??