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
>
