Re: [sqlite] How to know the offset of a rowid inside a table?
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 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
Re: [sqlite] How to know the offset of a rowid inside a table?
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 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
Re: [sqlite] How to know the offset of a rowid inside a table?
On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini 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
Re: [sqlite] How to know the offset of a rowid inside a table?
Hi, select count(*) from foo where rowid < X Martin Am 08.06.2011 16:18, schrieb Marco Bambini: > 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. > > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users