Re: [sqlite] How to know the offset of a rowid inside a table?

[Martin.Engelschalk]

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?

[Marco Bambini]

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?

[Richard Hipp]

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?

[Martin.Engelschalk]

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