Yes, sqlite 3.26. On Thu, Feb 14, 2019 at 4:33 PM Shawn Wagner <[email protected]> wrote:
> Are you using a new enough version of sqlite (3.25 or better) that you can > use window functions? > > On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde <[email protected] > wrote: > > > Hi! > > > > So I have been trying to write a query that needs to be really fast > (under > > 1 ms would be ideal) to a very large database (around 20 000 rows and 20 > > columns). The problem is that I need to find the user inside the sorted > > database and retrieve a number of users better and worse than him as to > > make a ranking table and show the user in the middle of it (the ranking > > table consists of 7 players). > > Currently this query takes about 3.5 ms and has a lot of issues. Some of > > them include the fact that I can't retrieve the users that have the same > > amount of points as my user. I also need to know the exact rank of the > > person inside the whole database that's why I need the count (*). It > would > > be ideal to have the ranks of every player in the database but I couldn't > > find a way that isn't ridiculously slow. Is there any fast way I can > > retrieve 3 players better or equal to my player with points and worse or > > equal to my player with points so there aren't any duplicates? > > > > select player.user_profile_id, (select count(*) + 1 from event_stats > > player2 where player2.points > player.points order by player2.points > desc) > > as 'rank', 'player' as 'stats_group', player.name, player.points from > > event_stats player where player.user_profile_id=202 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', > > stats.name, stats.points from event_stats user > > left join event_stats stats on stats.points > user.points > > where user.user_profile_id=202 order by stats.points ASC limit 3 > > ) q1 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', > > stats.name, stats.fame from event_stats user > > left join event_stats stats on stats.points < user.points > > where user.user_profile_id=202 order by stats.points DESC limit 3 > > ) q2 order by points desc > > > > Thanks for your help! > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
