SQL paging
A while back I wanted a nice paging in SQL with page and row number so I sat down and managed to figure this out…
USE Database SELECT * FROM (SELECT ((ROW_NUMBER() OVER (ORDER BY created DESC, id DESC)/10)+1) AS sqlpage, ROW_NUMBER() OVER (ORDER BY created DESC, id DESC) AS sqlrow, * FROM (SELECT id, created FROM TableName) AS data) AS paging WHERE sqlpage=1 ORDER BY sqlpage, sqlrow
((ROW_NUMBER() OVER (ORDER BY created DESC, id DESC)/10)+1) AS sqlpage calculates on which page the current row is in for the selected data and selected sorting (ORDER BY created DESC, id DESC). I’ve selected to have 10 rows per page that’s why I divide the ROW_NUMBER by 10. Since the page will be zero-indexed I increase the value with 1 so the first page number will be 1 and not 0.
Then I just added a simple ROW_NUMBER to get the current row number.
The data I’m using I get from SELECT id, created FROM TableName. Here I select which columns I want to return.
If you wish to get data for a specific page you just need to use WHERE sqlpage=1 as I do in the example.
Finally I just want to mention that I’m no SQL guru and don’t know if this is an optimized way to do this in SQL but here you have it anyway. Enjoy!
Hello world!
Welcome to my new blog!
I don’t know what will happen here or how often but most likely I will post some code snippets and such from time to time.
Hopefully I might help other people with tips and tricks for their programming otherwise it will be a good database for my snippets 
