Get section from query

Question

Say I have a table of 1 million video game highscores, with an integer column score. When a user asks for the top 50 scores, I do select * from highscores order by score desc limit 50. Simple. But what if the user asks for the 51 through 100th scores? I could select the top 100 and then, in my program interfacing the database, ignore the first 50 rows; but that would be wasteful. And what if they asked for the 200000th through 200100th rows? The inefficiency would only get worse.

Answer 1

In sql server (2008+)

you can use row_number() to get the range of rows

with topScores as
(
 select *,row_number() over(order by score desc) rowNum  from highscores  
)
select * from topScores where (rowNum between 1 and 100) -- or rowNum between 101 and 200

or use Dense_Rank() to consider the games with the same score as the same order -- like below

with topScores as
(
  select *,dense_rank() over(order by score desc) scoreOrder  from highscores  
)
select * from topScores where (scoreOrder between 1 and 100) -- or scoreOrder between 101 and 200

in sql server 2012 +

you can use OFFSET Fetch Clause (but it will not consider the games with the same score as one order) this example to get the high scores rows from 101 to 200

SELECT * FROM highscores ORDER BY score Desc OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

Answer 2

You can query the result of another query - it looks like you'll have to always do a select of the size of the larger number:

select * from (
    select * from highscores order by score desc limit 100
)
order by score asc limit 50

Here we select the top 100 scores, and from that, we select 50 of the lowest scores from that group of 100.

---

A better solution would be to add an index to the score column, and if you want the top x to y highscores you just pick everything from row x to row y. See this question about selecting rows by range.