SQLite query to compute row number without row_number etc

Question

I have a table of string values where the natural sort order is first by length and then by value.

i.e.,

CREATE TABLE t(v VARCHAR(255));

INSERT INTO t(v) VALUES ('100'), ('10'), ('8'), ('5'), ('3'), ('1');

SELECT v FROM t ORDER BY LENGTH(v) DESC, v DESC;

Gives this correct result:

100
10
8
5
3
1

Given this ordering I need the row number for each row, but I am operating in an environment (Android) with a version of SQLite that does not support ROW_NUMBER or other window functions.

Is this doable in plain SQL?

Suggestions like:

SELECT t1.v,
       (SELECT COUNT(*) FROM t t2 WHERE LENGTH(t1.v) <= LENGTH(t2.v) AND t1.v <= t2.v) - 1 AS rownum
  FROM t t1;

or variants like:

  SELECT t1.v, COUNT(t2.v) - 1 AS rownum
    FROM t t1
    JOIN t t2 ON LENGTH(t1.v) <= LENGTH(t2.v) AND t1.v <= t2.v
GROUP BY t1.v
ORDER BY LENGTH(t1.v) DESC, t1.v DESC;

do not work; the result is:

100|0
10|1
8|0
5|1
3|2
1|5

[This is why this is not a dupe of questions like https://stackoverflow.com/questions/48834215/row-number-without-using-the-row-number-window-function]

Answer 1

I have tried a slightly different variant of what you shared using self join.Compare for each row in t1 , count how many rows are present in t2 which are greater than that row in t1,which determines the rank for that row in t1.

Example :

for 3, values greater than 3 are 300,100,200,10,8,5 which places 3 at 6th rank.However 3 gets 7th rank because +1 is to rank the highest as 1 rather than 0 as 300 will not find a match and will be counted as 0.

INSERT INTO t(v) VALUES ('300') , ('200'),('100'), ('10'), ('8'), ('5'), ('3'), ('1');

Query

Fiddle Demo

SELECT t1.v, COUNT(t2.v) + 1 AS row_num      
FROM t t1
LEFT JOIN t t2
ON
    LENGTH(t1.v) < LENGTH(t2.v)
    OR (LENGTH(t1.v) = LENGTH(t2.v) AND t1.v < t2.v)
GROUP BY
    t1.v
ORDER BY
    LENGTH(t1.v) DESC, t1.v DESC;

Output

v	row_num
300	1
200	2
100	3
10	4
8	5
5	6
3	7
1	8