Am 08.06.2011 16:37, schrieb Marco Bambini: > Thanks Martin and Richard, solution was so simple that I think to need a time > break today. No, an order by does not make any sense here, because the result set contains exactly one row. > 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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
