asked
Need help with SQL Select statement (max over sum of records matching some criteria
For our bowling league, I have a database with several tables, one being: Results: PlayerID, Pins, Day Every player can have up to 3 result entries for a particular day (that's called series of 3 games). I'd like to have the maximum series for each player (i.e. the maximum of the sum of the 3 games with identical Day). Can that be done in a single SQL Select statement? With sample data: Results: (PlayerID, Pins, Day) 1, 190, 1 1, 195, 1 1, 200, 1 2, 197, 1 2, 180, 1 2, 196, 1 1, 200, 2 1, 205, 2 1, 175, 2 2, 185, 2 2, 199, 2 2, 197, 2 It should output: (PlayerID, MaxSeries) 1, 585 (for player 1, i.e. 190+195+200 of day 1, which is more than 200+205+175=580 of day 2) 2, 581 (for player 2, i.e. 185+199+197 of day 2, which is more than 197+180+196=571 of day 1) Thanks for your thoughs and assistance :-) Tom
Hi, Using a CTE is probably the way I would do this in one statement. I think the below script does what you're after. Any questions let me know. Cheers. IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results --create temp table CREATE TABLE #Results ( PlayerID int, Pins int, [Day] int ) --populate with test data INSERT INTO #Results VALUES(1, 190, 1), (1, 195, 1), (1, 200, 1), (2, 197, 1), (2, 180, 1), (2, 196, 1), (1, 200, 2), (1, 205, 2), (1, 175, 2), (2, 185, 2), (2, 199, 2), (2, 197, 2); --CTE to rank the players total score WITH RankingData AS ( SELECT ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY SUM(Pins) DESC) AS RowNum, PlayerId, [Day], SUM(Pins) AS MaxSeries FROM #Results AS td GROUP BY td.PlayerID, [Day] ) SELECT a.PlayerID , a.Day , a.MaxSeries FROM RankingData a WHERE a.RowNum = 1
Wow! Great! Perfect Solution! However, please forgive my ignorance, in real life I'd need to sum up more than one field, i.e. PinsNet and PinsGross (i.e. PinsNet plus a certain (changing) handicap). I thought I'd keep the question simple and extrapolate the solution myself, but I'm a bit puzzled and would appreciate your help once more. Expanding the sample data like that: IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results --create temp table CREATE TABLE #Results ( PlayerID int, PinsGross int, PinsNet int, [Day] int ) --populate with test data INSERT INTO #Results VALUES (1, 190, 180, 1), (1, 195, 185, 1), (1, 200, 190, 1), (2, 197, 187, 1), (2, 180, 170, 1), (2, 196, 186, 1), (1, 204, 189, 2), (1, 209, 194, 2), (1, 181, 166, 2), (2, 189, 174, 2), (2, 203, 188, 2), (2, 201, 186, 2); The output should then be: PlayerID MaxGrossSeries MaxNetSeries 1 594 555 2 593 548
You can SUM more columns in the CTE. If the net and gross scores differ by a fixed amount per player I guess you can use either for the ORDER BY clause of the ROW_NUMBER function. Your new query would be this (although I make MaxNet for player 1 549 not 555): WITH RankingData AS ( SELECT ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY SUM(PinsGross) DESC) AS RowNum, PlayerId, [Day], SUM(PinsGross) AS MaxPinsGross, SUM(PinsNet) AS MaxPinsNet FROM #Results AS td GROUP BY td.PlayerID, [Day] ) SELECT a.PlayerID , a.Day , a.MaxPinsGross , a.MaxPinsNet FROM RankingData a WHERE a.RowNum = 1
Thanks again for taking your time to look into this. The problem is, that the handicap changes per day and player. It can, therefore, be that one player has a high gross series one day, but that player's max net series is on another day. Hence MaxNet for player 1 is 555 on day 1, and his MaxGross in 594 on day 2. Tricky, huh? :-) As you can see, my SQL knowledge is still somewhat basic. Wouldn't it be possible to have two separate SELECTs, one for MaxNet and one for MaxGross, and then somehow join them based on the playerID, so that they show up as individual columns of the same record? As I do need to join the results to different tables not mentioned here, I'd still like to have all in one query. However, as there are only about 150 players, we could work with temp tables, if that makes it easier. (BTW, I do not need the day in the output - as the day would be different for the two Max's, that wouldn't make sense anyway.) Again, thank you SO MUCH for your help!
Yeah - you can do that. You can have 2 ROW_NUMBER columns - one that orders by gross and by net. You then join the resultant table to itself to get the 2 scores. Here's the new query: WITH RankingData AS ( SELECT ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY SUM(PinsGross) DESC) AS GrossRowNum, ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY SUM(PinsNet) DESC) AS NetRowNum, PlayerId, [Day], SUM(PinsGross) AS MaxPinsGross, SUM(PinsNet) AS MaxPinsNet FROM #Results AS td GROUP BY td.PlayerID, [Day] ) SELECT gross.PlayerID , gross.MaxPinsGross , net.MaxPinsNet FROM RankingData gross INNER JOIN RankingData net ON net.PlayerID = gross.PlayerID WHERE gross.GrossRowNum = 1 AND net.NetRowNum = 1
DECLARE @Player AS TABLE ( PlayerID INT, Pins INT, DayNo INT ) INSERT INTO @Player SELECT 1, 190, 1 UNION ALL SELECT 1, 195, 1 UNION ALL SELECT 1, 200, 1 UNION ALL SELECT 2, 197, 1 UNION ALL SELECT 2, 180, 1 UNION ALL SELECT 2, 196, 1 UNION ALL SELECT 1, 200, 2 UNION ALL SELECT 1, 205, 2 UNION ALL SELECT 1, 175, 2 UNION ALL SELECT 2, 185, 2 UNION ALL SELECT 2, 199, 2 UNION ALL SELECT 2, 197, 2 --SELECT * FROM @Player SELECT Q.PlayerID,Q.DayNo,Q.SUM FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY PLAYERID ORDER BY SUM DESC) ROWNO ,* FROM ( SELECT PlayerID,DayNO,SUM(PINS) SUM FROM @Player GROUP BY DAYNO,PLAYERID )P )Q WHERE Q.ROWNO =1
Thank you, Sunithay, that, too, works - for the initial question. Would you by chance know how to extend this for two different values like I outlined above? I have updated your solution to include the extra data: IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results --create temp table CREATE TABLE #Results ( PlayerID int, PinsGross int, PinsNet int, [Day] int ) --populate with test data INSERT INTO #Results VALUES (1, 190, 180, 1), (1, 195, 185, 1), (1, 200, 190, 1), (2, 197, 187, 1), (2, 180, 170, 1), (2, 196, 186, 1), (1, 204, 189, 2), (1, 209, 194, 2), (1, 181, 166, 2), (2, 189, 174, 2), (2, 203, 188, 2), (2, 201, 186, 2); SELECT Q.PlayerID, Q.MaxPinsGross FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PlayerID ORDER BY MaxPinsGross DESC) RowNo, * FROM (SELECT PlayerID, SUM(PinsGross) MaxPinsGross FROM #Results GROUP BY Day, PlayerID )P )Q WHERE Q.RowNo =1
Meanwhile, mjharper has provided one solution to the updated question. Still, your solution to the inital question is simpler in structure and I still wonder if the way you're suggesting still works for two different MaxSums... I hope it's ok and doesn't harm anyone if I select both solutions as answers?! I'm a newbie here, so please let me know if that's not fair for some technical reason... Uhh.... can't accept two answers. How can I reward both of you? mjharper was first and fully complete, sunithay has the shorter approach?
16 People are following this question.
