Actually I just need a unique number - but sorted in code now. Thank You
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 15:57, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
