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
>
