I recently was faced with a situation that needed to update a field in a table with a NUMERIC RANK based on values in certain column(s).
Traditionally we would have written scripts that were either PL\SQL to sort data etc. using cursors etc. and then update the field or write a program (in case of huge datasets) that sorted the data in a temporary dataset and then write update statements. Well….I did not want to do that, I wanted to write something short and sweet that could be reused and could scale in large datasets.
I started thinking about using a combination of the newer SQL Server features COMMON TABLE EXPRESSION, RANK( ) and UPDATE sql. I rank into a couple of roadblocks initially e.g. when I tried writing a common table expression in a scalar function – it did not allow that. In the end, I did get it working. I have also attached a demo SQL script use_cte_rank_toupdate.sql (1.4 KB) that is a self reliant script that demo's the idea.
VISUALISATION
Here are the steps – you can essentially replace each step with specific SQL’s that correspond to your business needs – but the high level steps\concepts can be followed.
STEP 1: Write a SQL Statement that first sorts your data using the RANK() function
STEP 2: Wrap the SQL Statement from Step 1 in a COMMON TABLE EXPRESSION WITH syntax
STEP 3: Write an UPDATE statement to update the original TABLE that joins the COMMON TABLE EXPRESSION with the ORIGINAL TABLE to update the NUMERIC RANK column
Essentially the idea is to use these new features to make development simpler - I hope this article spawns different ideas in your minds of leveraging your SQL features.