PY uttered:
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.
Why is this your expected result? Distinct applies to the whole row,
which includes the id. The view to get id and x is:
Create View v_foo AS
select distinct id,x FROM foo
ORDER BY x desc;
This will give you:
sqlite> select * from v_foo;
4|Z
2|Y
3|X
1|X
Each row is indeed distinct.
As you view is defined, you don't get the id at all, and distinct does
indeed return a single instance of X, Y and Z. If you want the x to be
unique, make it the primary key or create a unique index on it:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE );
The insert of the second x=X row will now fail:
sqlite> insert into foo(x) values('X');
sqlite> insert into foo(x) values('Y');
sqlite> insert into foo(x) values('X');
SQL error: column x is not unique
sqlite> insert into foo(x) values('Z');
sqlite>
Thanks,
VK
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
