CODEWITHSUNDEEP
sqliteinsert-select
Raceimaztion
Raceimaztion

Reputation: 9644

SQLITE equivalent for Oracle's ROWNUM?

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?

Upvotes: 15

Views: 37761

Answers (5)

sadegh salehi
sadegh salehi

Reputation: 37

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

Upvotes: 0

Aislan Gelatti Rocha
Aislan Gelatti Rocha

Reputation: 71

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

Upvotes: 4

Pyrollo
Pyrollo

Reputation: 201

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;

Upvotes: 15

Johannes Kuhn
Johannes Kuhn

Reputation: 15172

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

Upvotes: 14

user610650
user610650

Reputation:

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.

Upvotes: 8

Related Questions