CTE:
You can embed you SELECT
with RANK()
into a CTE and then UPDATE
the CTE.
WITH cte AS
(
SELECT *, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
FROM @data
)
UPDATE c
SET rank_in_department = r
FROM cte c;
Don't forget the ;
terminator at the end of the line preceding the CTE statement.
Sub Query:
You can also self JOIN
your table on a sub query with the expected RANK
.
UPDATE d SET rank_in_department = r.r
FROM @data d
INNER JOIN (
SELECT id
, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
FROM @data
) r ON d.id = r.id
This query expects an Id
or a group of column in both the sub query and the JOIN
. It is used to uniquely identify each row and JOIN
it to the table. From your data in your sample picture, this seems to be manager_code+, archive_year, archive_day_of_year
Sample Data used:
This gives your 2 correct syntaxes using this sample data. Queries must be adapted to your real table(s).
DECLARE @data TABLE(id int identity(0, 1), archive_year int, archive_day int, branch_code nvarchar(5), rank_in_department int, open_count int)
INSERT INTO @data(archive_day, archive_year, branch_code, open_count) VALUES
(2016, 1, 'X', 5)
, (2016, 1, 'X', 15)
, (2016, 1, 'X', 52)
, (2016, 1, 'X', 36)
, (2016, 1, 'X', 55)
, (2016, 1, 'Y', 65)
, (2016, 1, 'Y', 85)
, (2016, 1, 'Y', 42)
, (2016, 1, 'Y', 96)
, (2016, 1, 'Y', 15);
SELECT *
, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
FROM @data;