How to get the ROWID or row-order of a sorted table in SQLite?

Question

I need to sort a given table by one attribute and then store the order of this sorted table in one column of this table in SQLite. How can this be done?

Sample table:

CREATE TABLE t0 (name TEXT, sortId INT)
INSERT INTO t0 VALUES ('C', NULL)
INSERT INTO t0 VALUES ('A', NULL)
INSERT INTO t0 VALUES ('B', NULL)

Now I need to sort that table by attribute "name" and store the index of the sorted rows via UPDATE-command in column "sortId". The result should look like this:

name sortId
---- ------
C         3
A         1
B         2

How can this be done?

Answer 1

Use ROW_NUMBER() window function in a CTE to get the ranking of each row and join it to the table in the UPDATE statement:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY name) AS rn FROM t0)
UPDATE t0
SET sortId = cte.rn
FROM cte
WHERE cte.name = t0.name;

See the demo.

For older versions of SQLite without support of the UPDATE...FROM syntax or even window functions you can use a correlated subquery:

UPDATE t0
SET sortId = (SELECT COUNT(*) + 1 FROM t0 AS t WHERE t.name < t0.name);

See the demo.