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 <dobrila.su...@gmail.com 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
