Am 13.06.2006 um 04:14 schrieb PY:
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?
I once needed something similar, namely an ID-type column in a view
that joined several tables and I synthesized an ID like this:
CREATE VIEW foo AS
SELECT x,y, table1.id||'_'||table2.id AS ID
FROM table1
LEFT JOIN table2 ON table2.id=table1.id;
However, this will conflict with your distinct requirement - in that
case you may be able to solve this by going through a second view
that does the distinct selection on certain columns, while the first
one maintains the id...
Not sure whether this is what you need, but it was a solution for me
until I dropped it due to insufficient performance: the "id" column
is synthesized for each access, so when you have a large number of
rows and/or need to access the id column often, you'll find that this
won't yield optimal performance, especially when exetending the
concept to a larger number of tables...
YMMV
OTOH, if foo is the only table used in the view, why not use foo's id
column (or foo's rowid) as the ID?
HTH,
</jum>
