Re: [sqlite] How to determine player's leaderboard rank efficiently?

> > > Regarding: > > WITH RECURSIVE >x(y) AS (VALUES(1) UNION ALL SELECT y+1 FROM x WHERE y<1000) > INSERT INTO player SELECT printf('Player%d',i), i, random(), NULL FROM x; > > > If anyone's "playing along at home" I wondered if instead of: ... printf('Player%d',i), i,

Re: [sqlite] How to determine player's leaderboard rank efficiently?

Alessandro - THANK YOU! I've implemented this and item 2 is now averaging 0.02 ms. You made my weekend. Really appreciate your help as well as the input of the other folks. Tom -- View this message in context:

Re: [sqlite] How to determine player's leaderboard rank efficiently?

On Sun, Aug 24, 2014 at 3:13 AM, Keith Medcalf wrote: > > > c.execute("INSERT INTO player VALUES > ('Player%d',%f,NULL)"%(i,random.random())) > > >real5m29.157s > >user5m22.137s > >sys0m5.457s > > Not that it makes much of a difference since this is only for

Re: [sqlite] How to determine player's leaderboard rank efficiently?

> c.execute("INSERT INTO player VALUES > ('Player%d',%f,NULL)"%(i,random.random())) >real5m29.157s >user5m22.137s >sys0m5.457s Not that it makes much of a difference since this is only for "testing" but using: c.execute('INSERT INTO player VALUES (?,?,NULL);', ('Player%d' %

Re: [sqlite] How to determine player's leaderboard rank efficiently?

1) Create data (now I use a script in python to create 10 millions rows with random data) test.py: import random import sqlite3 db=sqlite3.connect('players.db') c=db.cursor() c.execute("BEGIN") c.execute("CREATE TABLE player(name PRIMARY KEY, score REAL, rank INTEGER)") for i in range(1000):

[sqlite] How to determine player's leaderboard rank efficiently?

Hi, I have a table of players each with a score. I need to do 2 things: 1. select top n players sorted on score - this is easy, just select stuff order by score desc limit n. For efficiency, use an index on score. The top guy is rank 1, the next guy is rank 2, etc. I only need to do this once a