How to set rank per group with a SQL UPDATE statement?

Question

Please first refer to the table structure:

Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year.

I need to rank the managers for every group which members having the same branch_code, year and day of year. Here branch_code stands for the department code.

I tried this:

I could get the correct rank number using RANK() on SQL Server but I don't know how to set the correct_rank back into the rank_in_department column using an UPDATE statement on table open_account_by_manager_per_day.

Do you have any idea on how this can be done?

Answer 1

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;