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
