Considering this table:
create table t (EventId int
,Section int
,PlayerName nvarchar(50)
,Score int
,Rank int
)
I trying to writing the T-SQL that does have EventId as input and using the RANK
function to rank by the score but with the sections separated (Ranking individual for each section, Rank 1 for highest score in each section and so on) and then setting/updating the Rank value
The RANK() function is a window function could be used in SQL Server to calculate a rank for each row within a partition of a result set. The same rank is assigned to the rows in a partition which have the same values.
To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause. SQL's RANK() function allows us to add a record's position within the result set or within each partition. In our example, we rank rows within a partition.
The rank of a row is one plus the number of ranks that come before the row in question. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).
The row_number gives continuous numbers, while rank and dense_rank give the same rank for duplicates, but the next number in rank is as per continuous order so you will see a jump but in dense_rank doesn't have any gap in rankings.
UPDATE tbl
SET [Rank] = t2.[Rank]
FROM tbl t1
LEFT OUTER JOIN
(
SELECT EventId
, Section
, PlayerName
, Score
, RANK() OVER (PARTITION BY EventId, Section ORDER BY Score desc) as [Rank]
FROM tbl
) as t2
ON t1.EventId = t2.EventId
AND t1.Section = t2.Section
AND t1.PlayerName = t2.PlayerName
Here it is running on SEDE.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With