In SQLite, I can add a row number column to an arbitrary table with
select rowid,* from tab;
for example -
sqlite> create table tab(content);
sqlite> insert into tab values('first row');
sqlite> insert into tab values('second row');
sqlite> insert into tab values('third row');
sqlite> select * from tab;
content
first row
second row
third row
sqlite> select rowid,* from tab;
rowid|content
1|first row
2|second row
3|third row
however this technique does not work with views -
sqlite> create view v(content) as select 'first row' union select 'second row' union select 'third row';
sqlite> select * from v;
content
first row
second row
third row
sqlite> select rowid,* from v;
rowid|content
|first row
|second row
|third row
In the toy example above, I could add a row number column when creating view v, but in practical use, my views are normally more complex table joins or recursive CTEs. How can I add a row number column to an arbitrary view?
