For this application I would suggest: DROP TABLE IF EXISTS query_results; CREATE TEMP TABLE query_results (...); INSERT INTO query_results SELECT <whatever> ORDER BY <however> LIMIT <count>;
SELECT rowid,* from query_results; DROP TABLE IF EXISTS query_results; SQLite implicitly assigns numerical ascending rowids to a table. You may also use INTEGER PRIMARY KEY to assign your own name to the rowid column. And if you need to backreference to the original table you could also store that table's rowid (which may change in certain cases). -----Ursprüngliche Nachricht----- Von: Dave Wellman [mailto:dwell...@ward-analytics.com] Gesendet: Montag, 01. Juli 2013 13:20 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) Hi, Where the "row number in the answer set" does come in useful (or at least where I've used it a number of itmes) is when populating a table with the results of a select and including this value as another column in that table. "build a table with the top 10 selling items over the last week" Insert into target-table Select productid ,sum(sales) ,row-number-in-answer-set as sales_rank >From sales_table... This involves a single sql request with no returning of data rows back to the application. Very easy to code and execute. The above table can then be compared to an equivalent list of products from the previous week - this then forms the basis of a lot of useful analysis. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alex Bowden Sent: 01 July 2013 12:07 To: General Discussion of SQLite Database Subject: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) > This would not be something you would sort by. And what if I do? > It should be assigned a value only during final 'display' of the query after all 'sorts' of operations are done with. Oh great. So the user is supposed to understand the implementation, in order to understand what the results will be. This would be just another nail in the coffin of relationality and simplicity, on a minor whim. On 1 Jul 2013, at 11:01, Tony Papadimitriou <to...@acm.org> wrote: > > -----Original Message----- From: Alex Bowden > Sent: Monday, July 01, 2013 12:46 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Is there a way to return the row number? (NOT therowid) > > > I can't wait to try > > order by row_number desc > > > On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote: > >> Is there a function (or method), e.g., row(), to return the sequence number of the selected row? This is not the same as ROWID. row() should give a sequence number always starting from 1 up the to the number of rows returned by the view/select etc. >> >> If not, then please add to the wish list. >> >> TIA >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
