SQLITE equivalent for Oracle's ROWNUM?

Question

I'm adding an 'index' column to a table in SQLite3 to allow the users to easily reorder the data, by renaming the old database and creating a new one in its place with the extra columns.

The problem I have is that I need to give each row a unique number in the 'index' column when I INSERT...SELECT the old values.

A search I did turned up a useful term in Oracle called ROWNUM, but SQLite3 doesn't have that. Is there something equivalent in SQLite?

Answer 1

select rowid, * from exampleTable

sqlite is already adding rowid for you, no need to do the same twice. Note that rowid will be reused by the sqlite after a LOT of inserts, when old ones are deleted.

Answer 2

No SQLite doesn't have a direct equivalent to Oracle's ROWNUM.

If I understand your requirement correctly, you should be able to add a numbered column based on ordering of the old table this way:

create table old (col1, col2);

insert into old values
    ('d', 3),
    ('s', 3),
    ('d', 1),
    ('w', 45),
    ('b', 5465),
    ('w', 3),
    ('b', 23);

create table new (colPK INTEGER PRIMARY KEY AUTOINCREMENT, col1, col2);

insert into new select NULL, col1, col2 from old order by col1, col2;

The new table contains:

.headers on
.mode column

select * from new;

colPK       col1        col2      
----------  ----------  ----------
1           b           23        
2           b           5465      
3           d           1         
4           d           3         
5           s           3         
6           w           3         
7           w           45

The AUTOINCREMENT does what its name suggests: each additional row has the previous' value incremented by 1.

Answer 3

You can use one of the special row names ROWID, OID or _ROWID_ to get the rowid of a column. See http://www.sqlite.org/lang_createtable.html#rowid for further details (and that the rows can be hidden by normal columns called ROWID and so on).