Details

Written by: Stanko Milosev

Category: MS SQL

Published: 15 August 2008

Last Updated: 01 March 2013

Hits: 6171

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