DECLARE @tbl TABLE ( EventId int, Section int, PlayerName...

0

Please login or register to vote for this query.

(click on this box to dismiss)

English

Q&A for linguists, etymologists, and serious English language enthusiasts

DECLARE @tbl TABLE
(
EventId int,
Section int,
PlayerName nvarchar(100),
Score int,
[Rank] int
)

INSERT INTO @tbl VALUES(1, 1, 'Should be Second', 1, -1)
INSERT INTO @tbl VALUES(1, 1, 'Should be First', 11, -1)
INSERT INTO @tbl VALUES(2, 1, 'Should be Third', 13, -1)
INSERT INTO @tbl VALUES(2, 1, 'Should be tied for First', 16, -1)
INSERT INTO @tbl VALUES(2, 1, 'Should be tied for First', 16, -1)
INSERT INTO @tbl VALUES(3, 1, 'Should be First', 17, -1)
INSERT INTO @tbl VALUES(1, 2, 'Should be First', 18, -1)

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

SELECT * from @tbl

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results