Of course, if the traversal order is different than the row return order then
you will not get ascending logical row numbers unless you do something like:
select logicalrow() as SequenceNumber,
t.*
from (...query ...) as t;
If you need logical row numbers it is much better (and far more efficient) to
assign them in your program as the results are returned.
Out of curiosity, why do you need logical result row numbers since they do not
correlate with anything meaningful?
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Saturday, 24 June, 2017 06:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Providing incrementing column to query
>
> Hmm thanks Clemens
>
> Have written an extension to do it - some of my tables are very big and
> feel that the extension might be a better route.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote:
>
> > Paul Sanderson wrote:
> > > I Have a number of queries to which I want to supply an incrementing
> > column,
> > > some of these queries involve without rowid tables.
> > >
> > > Is there a way of doing this with a SQL query?
> >
> > First, define a sort order, and ensure that it does not have duplicates.
> > Then use a correlated subquery to count how many rows would come before
> > the current one in that order:
> >
> > SELECT (SELECT count(*)
> > FROM MyTable AS T2
> > WHERE T2.name <= MyTable.Name
> > ) AS row_number,
> > name,
> > age
> > FROM MyTable
> > ORDER BY name;
> >
> > It would be a better idea to count returned rows in your program.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
