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
