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