ROWID like column with SELECT statement

(1) By anonymous on 2022-04-10 22:51:48 [link] [source]

The intention is to add a column recid that is a sequential integer that is unique to each row with an (another dialect's) SQL statement like

Either	select recid = identity(int,start,increment),* from myTable;
Or	create table myNewTable as select recid = identity(int,start,increment),* from myTable;

where recid is of type int and is a sequential number beginning with start and incrementing by increment

How do I achieve that with SQLite?

(2) By Keith Medcalf (kmedcalf) on 2022-04-11 00:24:55 in reply to 1 [link] [source]

Here is one way:

create table myNewTable as select ((row_number() over ())-1)*:increment+:start as recid,  
                                  *  
                             from myTable
;

(3.6) By cj (sqlitening) on 2022-04-11 01:31:31 edited from 3.5 in reply to 1 [source]

Syntax: ColumnName%Increment=0
Example: create table t2 as select * from t1 where c1>=4 and c1%2 = 0 

create table t1(c1 integer primary key)
insert into  t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
str_Low = "4"
str_Inc = "2"
sql = "create table t2 AS SELECT * FROM t1 where c1>=" + str_Low + " and c1%" + str_Inc + "=0"

(4) By anonymous on 2022-04-11 06:59:04 in reply to 2 [link] [source]

Thanks Keith.

Nice: for my typical requirement (start = 1, increment = 1) your SQL produces the results without explicit parameters:

SELECT ((ROW_NUMBER() OVER ())) RECID, *
FROM thisTable;

A script that demonstrates the result:

sqlite> WITH thisTable (c1)
   ...> AS (
   ...> VALUES ('Mon'), ('Tue'), ('Wed'), ('Thu'), ('Fri'), ('Sat'), ('Sun')
   ...> )
   ...> SELECT ((ROW_NUMBER() OVER ())) RECID, *
   ...> FROM thisTable;
RECID  c1
-----  ---
1      Mon
2      Tue
3      Wed
4      Thu
5      Fri
6      Sat
7      Sun
sqlite>