SQLITE equivalent for Oracle's ROWNUM?

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).

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.

Many people here seems to mix up ROWNUM with ROWID. They are not the same concept and Oracle has both.

ROWID is a unique ID of a database ROW. It's almost invariant (changed during import/export but it is the same across different SQL queries).

ROWNUM is a calculated field corresponding to the row number in the query result. It's always 1 for the first row, 2 for the second, and so on. It is absolutely not linked to any table row and the same table row could have very different rownums depending of how it is queried.

Sqlite has a ROWID but no ROWNUM. The only equivalent I found is ROW_NUMBER() function (see http://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/).

You can achieve what you want with a query like this:

insert into new 
    select *, row_number() over () 
    from old;