August 8, 2015 at 1:03 pm
Rather than posting all of my failed queries, I'll just show what I'm trying to do.
auctions
fields
ID,aucTitle
values
1,"Auction 1"
2,"Auction 2"
fields
id,amount,bidderID,aucID
values
1,100,1,1
2,100,2,2
3,105,3,1
fields
id,bidderName
values
1,"Groucho"
2,"Chico"
3,"Harpo"
Expected results
fields
aucID,topBid,bidderName
1,105,"Harpo"
2,100,"Chico"
August 8, 2015 at 8:37 pm
Please see the first link under "Helpful Links" in my signature line below for future posts.
Here's the readily consumable test data for this problem.
--===== Create the test tables and populate them with test data
CREATE TABLE #Auctions
(ID INT, AucTitle VARCHAR(20))
;
INSERT INTO #Auctions
(ID, AucTitle)
SELECT 1,'Auction 1' UNION ALL
SELECT 2,'Auction 2'
;
CREATE TABLE #Bids
(ID INT, Amount Int, BidderID INT, AucID INT)
;
INSERT INTO #Bids
(ID, Amount, BidderID, AucID)
SELECT 1,100,1,1 UNION ALL
SELECT 2,100,2,2 UNION ALL
SELECT 3,105,3,1
;
CREATE TABLE #Bidders
(ID INT, BidderName VARCHAR(20))
;
INSERT INTO #Bidders
(ID, BidderName)
SELECT 1,'Groucho' UNION ALL
SELECT 2,'Chico' UNION ALL
SELECT 3,'Harpo'
;
Here's one solution. I assumed, despite your requested output, that you also wanted the name of the auction.
WITH cteFindWinners AS
(
SELECT AucID, Amount, BidderID,
RowNum = ROW_NUMBER() OVER (PARTITION BY AucID ORDER BY Amount DESC)
FROM #Bids
)
SELECT w.AucID, a.AucTitle, TopBid = w.Amount, b.BidderName
FROM cteFindWinners w
JOIN #Bidders b ON w.BidderID = b.ID
JOIN #Auctions a ON w.AucID = a.ID
WHERE w.RowNum = 1
ORDER BY w.AucID
;
Here's the result of the above...
AucID AucTitle TopBid BidderName
----------- -------------------- ----------- --------------------
1 Auction 1 105 Harpo
2 Auction 2 100 Chico
(2 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2015 at 7:19 am
Jeff, thanks for the reply! As you no doubt noticed, I'm new here. I'll try you solution after church today.
August 9, 2015 at 10:00 am
No problem and please pardon my manners. I did notice you were new and meant to say "Welcome aboard" and here's a helpful hint but it was getting late and I got in a hurry to finish. So, "Welcome aboard" and glad to "meet" you. You actually did a lot better in providing information for your problem than most. The article I referred you to will help you take it to the next level for your next problem. If you provide readily consumable data, people usually trip over each other trying to help and you'll get some great coded responses.
Let me know how this possible solution works out for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2015 at 11:07 am
Jeff it works! I made the following changes so that i can merge it into one line and use it in a classic asp page. It might be more efficient as a view(?) but I'm not sure how to do that. Anyway, thank you!
SELECT w.AucID, a.AucTitle, TopBid = w.Amount, b.BidderName
FROM (
SELECT AucID, Amount, BidderID,
RowNum = ROW_NUMBER() OVER (PARTITION BY AucID ORDER BY Amount DESC)
FROM Bids
) w
JOIN Bidders b ON w.BidderID = b.ID
JOIN Auctions a ON w.AucID = a.ID
WHERE w.RowNum = 1
ORDER BY w.AucID
August 9, 2015 at 12:41 pm
Thanks for the feedback and glad it worked for you.
Actually, the code was on "one line" to begin with. The method I used is known as a CTE and it does the same as what you did except if moves the "derived table" from the FROM clause to the CTE. Of course, I know squat about ASP (classic or otherwise) and so it may not have been able to handle the CTE. Your way is just fine. It does the same thing, in this case.
You could, I suppose, change it to a View as you suggest. Just remember that putting ORDER BY in a view doesn't not guarantee that you'll get it in the correct order from the view. Leave the ORDER BY out of the view and, instead, include it in the code you use to call upon the view.
The next question I have is, do you understand how and why the code works?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2015 at 2:42 pm
Well, as i see it, the select in the CTE orders the bids by amount in descending order. ROW_NUMBER with PARTITION BY AucID groups them together by AucID and assigns row numbers starting at 1 to each individual group.
The main select statement asks for only those records from the CTE that were assigned row number 1.
How'd I do?
I really do appreciate your help and patience. This is for a one day only auction of donated items on our corporate intranet to benefit the cancer society. I wrote the auction system that we used last year. I wasn't as concerned with efficient code as I should have been. One view of a list of the auction items resulted in upwards of 30 sql calls. We had 600+ employees clicking refresh constantly near the end of the auction. Care to guess what happened? CRASH! We managed to split the load over several servers and finish the auction but I don't intend to let that happen this year. That 30+ sql calls will be 1 now thanks to you!
August 9, 2015 at 4:20 pm
You did good! Just wanted to make sure because you're the one that has to support it.
And thank you very much for explaining what it was for. Worthy cause, for sure!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply