When I create a table, SQLite enumerates the rows in the rowid column.
When I create a view, is there any way to enumerate the output rows?
For example, say I have a table:
create table Planets( Name text collate nocase );
insert into Planets values( 'Mercury' );
insert into Planets values( 'Venus' );
insert into Planets values( 'Earth' );
insert into Planets values( 'Mars' );
insert into Planets values( 'Jupiter' );
How could I give those rows with enumeration:
1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus
In the absence of any other replies, the best I've come up with so far
is:
create view Enumerated
as
select count(*) as Sequence, Current.Name as Name
from Planets as Current
left join Planets as Others
where Current.Name >= Others.Name
group by Current.Name
;
It works by basically counting how many Other records are less than or
equal to the Current record.
If there was some view, say "Filtered", that needed to be enumerated,
I could replace "planets" in the above view with "Filtered" but I'd
still have to re-write the "order by" clause as a comparison, which
ignores the work already done by the "order by".
Is there a better way?
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
