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

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -