Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Ian Hardingham 

Hey Igor, thanks for the reply.

Is this O(1) ?

Ian

On 09/09/2011 13:21, Igor Tandetnik wrote:

Ian Hardingham  wrote:

Again, I have:

eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo
FLOAT)

with:

CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC)

If I have the id of a row in eloResultTable, I wish to find how far down
the eloResultScore index it is (I basically want to find a player's rank
when ordered by elo).  Is there a way to do this?

select count(*) from eloResultTable where elo>=
 (select elo from eloResultTable where id = ?);



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Simon Slavin 

On 9 Sep 2011, at 1:47pm, Ian Hardingham wrote:

> I really want O(1), but selecting by elo DESC and then setting a ranking 
> column for all records seems to be very slow, even during a transaction.  Any 
> tips for doing that fast?

If you're trying to collect this information for many different records there 
no need to run that SELECT many times.  Simply read your records ORDER BY old 
DESC and the first record will be ranked first, the second will be ranked 
second, etc..  There's no need to make SQLite do the numbering for you.

If you need to store the ranking with the record, just read all the rowids into 
a big array, then use their position in the array to work out how to update the 
ranking fields.

Also, you need to decide whether there's any point in storing these scores, or 
you can just calculate the score for a record when someone wants to see that 
particular record.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Ian Hardingham 

Thanks Igor.

I really want O(1), but selecting by elo DESC and then setting a ranking 
column for all records seems to be very slow, even during a 
transaction.  Any tips for doing that fast?


Thanks,
Ian

On 09/09/2011 13:34, Igor Tandetnik wrote:

Ian Hardingham  wrote:

Hey Igor, thanks for the reply.

Is this O(1)?  Or... I guess it's probably low-magnitude O(log n) ?

It's O(n). There's no shortcut to count(*), it walks through and counts all the 
records satisfying the condition. A B-tree doesn't provide a fast way to obtain 
a number of all the elements smaller than a given element.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Igor Tandetnik 
Ian Hardingham  wrote:
> Hey Igor, thanks for the reply.
> 
> Is this O(1)?  Or... I guess it's probably low-magnitude O(log n) ?

It's O(n). There's no shortcut to count(*), it walks through and counts all the 
records satisfying the condition. A B-tree doesn't provide a fast way to obtain 
a number of all the elements smaller than a given element.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Ian Hardingham 

Hey Igor, thanks for the reply.

Is this O(1)?  Or... I guess it's probably low-magnitude O(log n) ?

Ian

On 09/09/2011 13:21, Igor Tandetnik wrote:

Ian Hardingham  wrote:

Again, I have:

eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo
FLOAT)

with:

CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC)

If I have the id of a row in eloResultTable, I wish to find how far down
the eloResultScore index it is (I basically want to find a player's rank
when ordered by elo).  Is there a way to do this?

select count(*) from eloResultTable where elo>=
 (select elo from eloResultTable where id = ?);



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting the location of a record in an Index

2011-09-09 Thread Igor Tandetnik 
Ian Hardingham  wrote:
> Again, I have:
> 
> eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo
> FLOAT)
> 
> with:
> 
> CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC)
> 
> If I have the id of a row in eloResultTable, I wish to find how far down
> the eloResultScore index it is (I basically want to find a player's rank
> when ordered by elo).  Is there a way to do this?

select count(*) from eloResultTable where elo >=
(select elo from eloResultTable where id = ?);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting the location of a record in an Index

2011-09-09 Thread Ian Hardingham 

Hey guys.  (Thanks, I got my previous problem sorted).

Again, I have:

eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo 
FLOAT)


with:

CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC)

If I have the id of a row in eloResultTable, I wish to find how far down 
the eloResultScore index it is (I basically want to find a player's rank 
when ordered by elo).  Is there a way to do this?


(An example if that isn't clear - if id 1234 is the third highest elo, I 
wish to get the number "3" from this calculation).


Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users