I have a similar problem (I am using SQLite as a on disk local cache for a list of about 1,000,000 rows and then filtering and sorting that list as needed) and a slightly different solution, The best way I have come up with so far is to create a virtual table with a INTEGER PRIMARY KEY on it - IE
CREATE TEMP TABLE t ( id INT PRIMARY KEY, v VARCHAR(10) ); INSERT INTO t (v) SELECT DISTINCT x FROM foo; The advantage for me, since each filtered/sorted list has a reasonably short life time, is that I can then update the underlying table (which occurs relatively frequently) and then notify myself and update/recreate my temp tables (you could do this via a trigger) and I also have guaranteed unblocked reads from my temp table during updates of the original table allowing me to have near atomic updates of my sub lists (by creating a new temp table, swaping which one I use and then deleting the old one). Not perfect but it seems to work so far.. - James -----Original Message----- From: Christian Nassau [mailto:[EMAIL PROTECTED] Sent: 13 June 2006 07:41 To: sqlite-users@sqlite.org Subject: [sqlite] Re: How to realize the ROWID in a view? I think you could use min(rowids) as your new rowid like this: sqlite> create view v as select x,min(id) as id from foo group by x; sqlite> select * from v; x id X 1 Y 2 Z 4 Does this do what you want? PY wrote: > Hi All, > > I have a problem about the ROWID in a view. I want to simulate a ROWID in a > view just like the same purpose in a table. > > For Example: > > Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT); > insert into foo(x) values('X'); > insert into foo(x) values('Y'); > insert into foo(x) values('X'); > insert into foo(x) values('Z'); > > Create View v_foo AS > SELECT distinct(x) FROM foo > ORDER BY x desc; > > > SELECT * from foo; > > id x > ---------- ---------- > 1 X > 2 Y > 3 X > 4 Z > > > My expect result of "select * from v_foo;" is > > id x > ---------- ---------- > 1 Z > 2 Y > 3 X > > > > Would you please help to tell me how to finish that? > Thanks for your grest help. > > > > > Thanks, > VK >