Simple query construction

1

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.

2

Are you using SQL Server or some other dbms?

3

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
1 Like
4

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?

5

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
6

Btw, in modern SQL, the ROW_NUMBER() approach is considered a better practice.

7

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.