Tuesday, January 31, 2012

Paging for MS SQL query

It's a usual task get some records from a big table. MySql has a simple operator Limit offset,pagesize
If you want to get 16-20 records from the table Customers you write in MySql
SELECT * FROM Cbstomers LIMIT 15,5

It's a not trivial task for MS SQL query. We should write the next query :

SELECT * FROM (SELECT TOP 5 CustomerID,FullName From (SELECT TOP 20 Customers.CustomerID, Customers.FullName
FROM Customers
ORDER BY Customers.CustomerID ASC) As T2 Order by CustomerID DESC) ORDER BY CustomerID ASC;

page size=5
current page=4
current page x page size=20