sql - Get section from query -
say have table of 1 million video game highscores, integer column score
. when user asks top 50 scores, select * highscores order score desc limit 50
. simple. if user asks 51 through 100th scores? select top 100 , then, in program interfacing database, ignore first 50 rows; wasteful. , if asked 200000th through 200100th rows? inefficiency worse.
in sql server (2008+)
you can use row_number() range of rows
with topscores ( select *,row_number() over(order score desc) rownum highscores ) select * topscores (rownum between 1 , 100) -- or rownum between 101 , 200
or use dense_rank() consider games same score same order -- below
with topscores ( select *,dense_rank() over(order score desc) scoreorder highscores ) select * topscores (scoreorder between 1 , 100) -- or scoreorder between 101 , 200
in sql server 2012 +
you can use offset fetch clause (but not consider games same score 1 order) example high scores rows 101 200
select * highscores order score desc offset 100 rows fetch next 100 rows only;
Comments
Post a Comment