Hello all. I have a very simple SQL table (dbo.DailyTotals) that stores game scores. Field names are 'dt_UID','Gamedate','Player' and 'TotalScore'. There are 6 players, so each gamedate has 6 records - 1 per player. I want to create a query that returns the highest score and it's associated player for each day. I know this should be an easy to construct query, but have been unable to figure it out.
Are you using SQL Server or some other dbms?
Most dbms's should be able to handle this query. I have done it so that both (all) players list if a tie. If you don't want to list multiples even when there is a tie, change the "DENSE_RANK" to "ROW_NUMBER".
;WITH cte_daily_max AS ( SELECT *, DENSE_RANK() OVER(PARTITION BY Gamedate ORDER BY TotalScore DESC) AS rank_num FROM dbo.DailyTotals ) SELECT Gamedate, Player, TotalScore AS MaxScoreForDay FROM cte_daily_max WHERE rank_num = 1
Thanks so much Scott. This works perfectly, and is just what I was after. Out of curiosity, is it possible to accomplish the same result by using either a subquery (to select only highest score per day) or an inner join, aliasing the same table?
Here's a method with join.
SELECT DT.Gamedate, DT.Player, DT.TotalScore FROM dbo.DailyTotals DT INNER JOIN ( SELECT Gamedate, MAX(TotalScore) AS TotalScore FROM dbo.DailyTotals GROUP BY Gamedate ) GMS ON GMS.GameDate = DT.GameDate AND GMS.TotalScore = DT.TotalScore ORDER BY DT.Gamedate
And with subquery:
SELECT DT.Gamedate, DT.Player, DT.TotalScore FROM dbo.DailyTotals DT WHERE DT.TotalScore = ( SELECT MAX(TotalScore) AS TotalScore FROM dbo.DailyTotals DT2 WHERE DT2.Gamedate = DT.Gamedate ) ORDER BY DT.Gamedate
Btw, in modern SQL, the ROW_NUMBER() approach is considered a better practice.
Thanks Scott. This is valuable information for me. I had been trying both the subquery and inner join approaches, all with no luck. Since I'm now retired, I tinker with SQL just to try to stay sharp. I'll definitely be keeping your advice for future reference. Your advice is greatly appreciated.