On 1 Jul 2013, at 2:00pm, Gabriel Corneanu <gabrielcorne...@gmail.com> wrote:
> As I wrote it's easy to do when you control the loop. > Sometimes there is either no explicit loop (direct sql, no programming code) > or the loop is out of your reach (3rd party library). Sorry, I posted that before seeing the rest of the thread which built up afterwards. Nevertheless all solutions I see fail in systems with multiprocessing or multiple users, where the database may be edited between when the numbers are produced and when they’re used. It’s much better to have your software number the rows itself, or to use SQLite to store your ordinals in the tables. On 1 Jul 2013, at 12:19pm, Dave Wellman <dwell...@ward-analytics.com> wrote: > 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... Do SELECT productid FROM sales_table ORDER BY total_sales DESC LIMIT 10 Built the answers up in an internal variable, then use UPDATE to write the ranks back out again. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
