Hi Kees, He is telling about the Rowid the unique number that represents each row in the table, not about a table column named "ID" or anything else, or the primary key of the table.
[]'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Kees Nuyt wrote: > Hi Lokesh, > > On Mon, 3 Sep 2007 15:30:10 +0530, you wrote: > > >> This I know, but the thing is, I want the ROWID >> in VIEW to be sequential even after a SELECT with >> some condition has been executed, ie., from 1 to n. >> Just like in normal table. >> In your case it is not like that. >> > > If you delete rows from a table the tables' rowid isn't > consecutive anymore: > > CREATE TABLE testTbl( > t_id INTEGER PRIMARY KEY, > t_name TEXT > ); > INSERT INTO testTbl VALUES( 1, 'd1' ); > INSERT INTO testTbl VALUES( 2, 'd2' ); > INSERT INTO testTbl VALUES( 3, 'd3' ); > INSERT INTO testTbl VALUES( 4, 'd4' ); > SELECT * FROM testTbl; > 1|d1 > 2|d2 > 3|d3 > 4|d4 > DELETE FROM testTbl WHERE t_id=2; > SELECT * FROM testTbl; > 1|d1 > 3|d3 > 4|d4 > > >> By the way, what I mean to say is, why >> don't we have default ROWID in >VIEW >> like as in normal TABLE. >> > > Because a view isn't a table. > > By the way, the concept of rowid is not in the SQL standard. > It is a physical property (the B-Tree key) which rows happen to > have when they are stored the SQLite way. It has no other > meaning. A member of a set doesn't have an ordinal number in > relational theory. > > Richard Hipp made rowid visible because some tight embedded > applications can be speeded up nicely by using it. > Any code which uses the rowid is not portable, though. > > The number of a row in a view is its order of its occurence. > The first row has number 1 > The second row has number 2 > etc. > > It is easy to materialize that number in any language you will > use around your SQL, even in a shell: > > sqlite3 your.db "select * from testTbl;" | \ > awk -v OFS='|' '{print NR,$0}' > > 1|1|d1 > 2|3|d3 > 3|4|d4 > > note: \ is linewrap > > Regards, > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
