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?

Purehearted

Answer 1

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

Nyssa

SELECT rowid FROM <TableName> should return you the unique row number for every record. This is equivalent to ROWNUM. – Angora

Not exactly. Oracles ROWNUM applies to the result of the query. SELECT rownum FROM tbl WHERE rownum > 3 will find nothing (because the first row where this would be true get the rownum 1). But this is not the OPs requirement. He just needs an unique value. – Nyssa

Ok, it may not be "exactly" same as ROWNUM but it is the unique id for each row added automatically. This is @Raceimaztion's requirement. – Angora

Answer 2

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;

Bashan

Answer 3

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.

Ternan

Completely misses the point. ROWNUM is dynamic, applies to all tables. – Veronaveronese

Answer 4

I believe you want to use the constrain LIMIT in SQLite. SELECT * FROM TABLE can return thousands of records. However, you can constrain this by adding the LIMIT keyword.

SELECT * FROM TABLE LIMIT 5; Will return the first 5 records from the table returned in you query - if available

Semolina

That's the only correct answer! – Isolation

Answer 5

use this code For create Row_num 0....count_row

SELECT (SELECT COUNT(*)
FROM main AS t2
WHERE t2.col1 < t1.col1) + (SELECT COUNT(*) 
  FROM main AS t3 
  WHERE t3.col1 = t1.col1 AND t3.col1 < t1.col1) AS rowNum, * FROM Table_name t1  WHERE rowNum=0 ORDER BY t1.col1 ASC

Gelatinous