The title may be slightly vague... however, this is the basis of what I want to do: I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to lowest, which I can already do, but this is where I run into an issue.
I would like to then have said query find a specific bit of data, a row with a specific 'Name' as defined in the query, then get 5 rows that would be above it, and 5 rows that would be below it. It would also be helpful if it does this in a timely fashion. If you need more detail, just ask!
For anyone wondering what research I've done, I've found queries that will do this, however those queries use full table scans, which would not be optimal in this case as there is 123k+ rows of data. Also, this is in mysql.
My current query that does a full table scan and doesn't work extremely well, nor effeciently, in general:
SELECT allusers.rank, allusers.Name, allusers.Kills FROM ( SELECT AgStats.Name, @t:=IF(score<=>@s,@t,@r) AS rank, @s:=AgStats.Kills AS Kills, @r:=@r+1 FROM AgStats, (SELECT @r:=0, @s:=NULL, @t:=NULL) init ORDER BY AgStats.Kills DESC ) allusers, ( SELECT COUNT(*) AS rank FROM AgStats WHERE Kills > (SELECT Kills FROM AgStats WHERE Name = 'VoidWhisperer') ) user WHERE ABS(allusers.rank-user.rank) <= 5 ORDER BY allusers.rank
To give an example of what I'm trying to do: You have 10 rows of data, with the row kills on the end. I want to select a specific row out of those 10 based on name, then get a number of rows above it, in this case, 5 rows above/below, done via the query?