SQLTeam.com | Weblogs | Forums

Simple query construction

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

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.