I did try to do something with rank() and row_number() but it slowed the query a lot bringing it up to 26 ms.
> On 14 Feb 2019, at 16:33, Shawn Wagner <shawnw.mob...@gmail.com> 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 <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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
