mysql limit x, y equivalent for SQL Server l?

I need to write the code for pager on the web-page and usually with mysql it is simple solutions to SELECT with LIMIT 60, 20 which means select from 60 position 20 rows.

Now I'm working with SQL Server, but should to solve this problem - I know that the TOP syntax is unsuitable, I've also heard about ROW_NUMBER() function in SQL Server, but the examples that I'd found wasn't clear enough - please help on the test table like "cars" to sim开发者_JAVA百科ply

select * from cars limit 20, 10

ROW_NUMBER() can't be in your where clause, so you have to use a separate select:

select *
from (select row_number() over (ORDER BY cars.CarId) as Row, *
    from cars
    ) temp
where Row between 20 and 29

SQL Server 2005 introduced new ROW_NUMBER() function that makes paging task easier. To achieve paging like in previous examples, get third page from Customers table where Country column is 'Spain' with 10 records per page and ordered by CompanyName stored procedure will look like this:

CREATE PROCEDURE Paging_Customers
(
  @SelectedPage int,
  @PageSize int
)
AS
BEGIN
  WITH CTE_Customers(PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country)
  AS
  (
  SELECT CEILING((ROW_NUMBER() OVER
  (ORDER BY CompanyName ASC
  AS PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country
  FROM Customers
  )

SELECT *
FROM CTE_Customers WHERE PageNumber = @SelectedPage
END

Then, we call this procedure (for third page and ten rows per page) with this simple line:

EXEC Paging_Customers 3, 10

-- Sample data
;with cars(Name) as
(
  select 'SAAB' union all
  select 'Volvo' union all
  select 'Opel' union all
  select 'Ford'
)  

-- Query using row_number
select *
from 
  (
   select *,
          row_number() over(order by Name) as rn
   from cars
  ) as C
where C.rn between 2 and 3   

Result:

Name  rn
----  --
Opel  2
SAAB  3

Try this:

SELECT * FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY col1) AS row FROM sys.databases) table1 
WHERE row > 20 and row <= 30

More answers provided here on SO.