SQLite Forum

I agree with Larry that your problem is ill-defined, and Simon's answer is as good as any,  
but given that I'm a novice at [CTEs][1] and [Window Funtions][2], I thought I'd give it a try, just for fun.

Windows functions have `row_number()` which seems to be what you want. See the `select` below.  
Once you have that, then you need to update the table by *joining* with that result (it's a  
correlated subquery in fact, but that's the same idea). Below I assumed your order was `idx`,  
but just change the windowing to whatever order you actually want.

There may be a better way, and it may not be fast. But at least it seems to work :)

[1]: https://sqlite.org/lang_with.html
[2]: https://sqlite.org/windowfunctions.html

```
C:\Users\ddevienne>sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(idx integer primary key, txt text, ord integer);
sqlite> insert into t(idx, txt) values (10,'aa'), (11,'bb'), (12,'cc'), (13,'dd'), (14,'ee');
sqlite> .mode box
sqlite> select * from t;
┌─────┬─────┬─────┐
│ idx │ txt │ ord │
├─────┼─────┼─────┤
│ 10  │ aa  │     │
│ 11  │ bb  │     │
│ 12  │ cc  │     │
│ 13  │ dd  │     │
│ 14  │ ee  │     │
└─────┴─────┴─────┘
sqlite> select idx, txt, row_number() over (order by idx) as win_ord from t order by idx;
┌─────┬─────┬─────────┐
│ idx │ txt │ win_ord │
├─────┼─────┼─────────┤
│ 10  │ aa  │ 1       │
│ 11  │ bb  │ 2       │
│ 12  │ cc  │ 3       │
│ 13  │ dd  │ 4       │
│ 14  │ ee  │ 5       │
└─────┴─────┴─────────┘
sqlite> with tt(idx, win_ord) as (select idx,row_number() over (order by idx) from t)
   ...> update t set ord = (select win_ord from tt where tt.idx = t.idx);
sqlite> select * from t;
┌─────┬─────┬─────┐
│ idx │ txt │ ord │
├─────┼─────┼─────┤
│ 10  │ aa  │ 1   │
│ 11  │ bb  │ 2   │
│ 12  │ cc  │ 3   │
│ 13  │ dd  │ 4   │
│ 14  │ ee  │ 5   │
└─────┴─────┴─────┘
sqlite>
```