Question is how to show limited number of records on one page, like MySQL limit?
There is query which you can use like:
select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc) as newtbl order by lname desc
) as newtbl2 order by lname asc
Taken from here.
But I don't like that approach because with complicated queries things can be more complicated.
I rather took something like this:
WITH Ordered AS(
SELECT ROW_NUMBER()
OVER (ORDER BY OrderID) AS RowNumber,
OrderID, OrderDate FROM Orders
)
SELECT * FROM Ordered WHERE RowNumber between 21 and 30
Taken from here. It works only in MS SQL Server 2005 and above.
Unfortunately, WITH approach I couldn't use on dhtmlx.
---
With MS SQL Server 2012, we have new keyword offset fetch:
SELECT * FROM myTable order by keyWord offset 0 rows fetch next 5 rows only
and here is example with conditional order:
declare @myCol int = 0; SELECT * FROM myTable order by case @myCol when 0 then 0 end offset 25000000 rows fetch next 5 rows only GO
from here