Re: [sqlite] Enumerating rows in a view
Hi Darren, If that works, then try using a subquery in the view definition instead. create view Enumerated as select rowid as Sequence, Name from ( select Name from Planets order by Name ) Sort of like that. Thanks for the suggestion, but, unless I'm missing something, it doesn't work. Since rowid doesn't exist in the inner query, the outer query assigns Sequence with a null value, so the whole result is: SequenceName -- -- . Earth . Jupiter . Mars . Mercury . Venus (. = null) If we instead change it to include rowid in the subquery: create view Enumerated as select rowid as Sequence, Name from ( select rowid, Name from Planets order by Name ) ; we get the original rowids (but we instead want a numerical sequence 1, 2, 3, 4, 5): SequenceName -- -- 3 Earth 5 Jupiter 4 Mars 1 Mercury 2 Venus Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Enumerating rows in a view
At 1:08 AM +1000 8/26/07, T&B wrote: When I create a view, is there any way to enumerate the output rows? Another method would I've developed/discovered is to create a temporary table, fill it with the data from the view, then use the automatically created rowid column as the enumeration. This works, but is not ideal since the creation of a temporary table can't be included in a view itself. If that works, then try using a subquery in the view definition instead. create view Enumerated as select rowid as Sequence, Name from ( select Name from Planets order by Name ) Sort of like that. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Enumerating rows in a view
Hi All, Again following up: 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? Another method would I've developed/discovered is to create a temporary table, fill it with the data from the view, then use the automatically created rowid column as the enumeration. This works, but is not ideal since the creation of a temporary table can't be included in a view itself. So, to solve the previous example: create view "Planets Sorted" as select Name from Planets order by Name; we could do something like: begin immediate ; create temporary table Enumerated as select * from "Planets Sorted" ; select rowid as Sequence, * from Enumerated ; commit ; which gives the desired: 1 Earth 2 Jupiter 3 Mars 4 Mercury 5 Venus This "create temporary table" method is the same syntax for any view, unlike the "deconstruct the order into comparisons" method that I posted before which requires adding custom comparison operators. But the temporary table can't be included in a view, whereas the deconstruct method can. So neither is ideal but the best I've got so far. Any better alternatives welcome :-) Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Enumerating rows in a view
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] -
[sqlite] Enumerating rows in a view
Hi all, 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' ); and I have a view that sorts them alphabetically: create view as select Name from Planets order by Name; which gives: Earth Jupiter Mars Mercury Venus How could I give those rows with enumeration: 1 Earth 2 Jupiter 3 Mars 4 Mercury 5 Venus Note that the sort order may be more complex than this, or there may be a "where" filtering. But the result needs to simply number the rows from 1 to n. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -