Create auto-increment field in sqlite

Question

I am trying to create the correct select to have the row number for an ordered value in a table. Right now, the output does have the row_num field for the count but I would like to have the count sorted ascending based on on the value column before it is created.

If possible, how can this be done?

Existing code:

select id, value, (select count(*)
   from tbl b where a.id >= b.id) as row_num
from tbl a order by value asc

Current output (no ordering): Fiddle

id | value | row_num
1  | jbl   | 1
3  | bog   | 2
4  | tak   | 3
6  | oza   | 4
8  | ars   | 5

Current output (with ordering on value): Fiddle

id | value | row_num
8  | ars   | 5
3  | bog   | 2
1  | jbl   | 1
6  | oza   | 4
4  | tak   | 3

Desired output:

id | value | row_num
8  | ars   | 1
1  | bog   | 2
3  | jbl   | 3
6  | oza   | 4
8  | tak   | 5

There is a similar thread (How to use ROW_NUMBER in sqlite) but the value col was not sorted.

Thanks.

Answer 1

Use the row_number() window function.

SELECT id
     , value
     , row_number() OVER (ORDER BY value) AS row_num
FROM tbl
ORDER BY value;