Yu Tant

Home
query

Add Absolute Row Number on First Field

This is first problem what i'm doing with showing number on first field every table, now i get from roberto on stackoverflow how to get the absolute record number of a row after it has been returned by a query.

Let's take this example

sqlite> create table my_example (field1);
sqlite> insert into my_example values ('abc');
sqlite> insert into my_example values ('booooo');
sqlite> insert into my_example values ('3231-556');
sqlite> .mode column
sqlite> .header on
sqlite> select * from my_example;
field1
----------
abc
booooo
3231-556
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
1           abc
2           booooo
3           3231-556
sqlite> delete from my_example where field1='abc';
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
2           booooo
3           3231-556
sqlite> insert into my_example values ('abc');
sqlite> select rowid, * from my_example;
rowid       field1
----------  ----------
2           booooo
3           3231-556
4           abc
sqlite>

a way of getting what I understand you want to achieve (something similar to a rown_num) would be:

sqlite> select (select count(*) from my_example b where a.rowid >= b.oid ) as cnt, * from my_example a;
cnt         field1
----------  ----------
1           booooo
2           3231-556
3           abc
sqlite>