Re: [sqlite] Enumerating rows in a view

[T]

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

[Darren Duncan]

At 1:08 AM +1000 8/26/07, T 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

[T]

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

[T]

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

[T]

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]
-