Thanks for you reply.
I Just want to get a sequence number in a view, that is not the ID field in
the table foo.
Table foo is a sample of mine. In fact, my table is not only the id and x
field.
And the x field could be duplicate in the table foo.
Could you help me to finish that?
Thanks.
On 6/12/06, Christian Smith <[EMAIL PROTECTED]> wrote:
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
/ \
