SQLite - Assign a sequential number to each range of rows

Question

In my table every color has index, which defines color order inside country, in every country order may be different.

country	color	color_order	other_data
Canada	green	1
Canada	green	1
Canada	green	1
Canada	red	2
Canada	red	2
Canada	yellow	3
Canada	yellow	3
France	red	1
France	blue	2
France	blue	2

After removing one of colors (all 'red' rows), I need to re-number color_order for each country. Expected result:

country	color	color_order	other_data
Canada	green	1
Canada	green	1
Canada	green	1
Canada	yellow	2
Canada	yellow	2
France	blue	1
France	blue	1

It should be something like nested loop to iterate through country/color, seems query should include: ROW_NUMBER() OVER (PARTITION BY country ORDER BY color_order)

Any ideas please?

Answer 1

The DENSE_RANK() is a window function that computes the rank of a row in an ordered set of rows and returns the rank as an integer. The ranks are consecutive integers starting from 1. Rows with equal values receive the same rank. And rank values are not skipped in case of ties.

My solution looks like:

WITH cte AS (
  SELECT id, country, color, color_order, 
         DENSE_RANK() OVER (PARTITION BY country ORDER BY color_order ) AS new_order 
  FROM table1
)
UPDATE table1 SET color_order = (SELECT new_order FROM cte WHERE table1.id = cte.id );