Thanks Martin and Richard, solution was so simple that I think to need a time break today. Should I add an ORDER BY rowid clause at the end of the SELECT statement or its implicit by the WHERE clause?
Thanks a lot. -- Marco Bambini http://www.sqlabs.com On Jun 8, 2011, at 4:27 PM, Richard Hipp wrote: > On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> I have a table foo with N rows and I want to know the offset of the row >> with rowid X inside that table. >> What query/strategy should I perform? >> >> I assume that a brute force algorithm should be >> 1. SELECT * FROM foo ORDER BY rowid; >> 2. loop inside the recordset until X is found incrementing a counter by 1 >> but I am quite sure that should be a more elegant way. >> >> > SELECT count(*) FROM foo WHERE rowid<=X > > The above gives an answer in linear time. It is theoretically possible to > add a little extra metadata to the btree nodes in order to compute the > offset logarithmic time. But I deliberately decided not to included that > metadata when I designed the SQLite btree file format since keeping that > metadata current slows down write performance. > > > >> Thanks a lot. >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> >> >> >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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
