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,
--
( Kees Nuyt
)
c[_]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
