mysql limit x, y equivalent for SQL Server l?

Question

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 simply

select * from cars limit 20, 10

possible duplicate of [Row Offset in MS SQL Server](http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server) — Andriy M, Jul 05 '11 at 06:31
See also http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000 — Bill Karwin, Jul 05 '11 at 06:34

score 3 · Accepted Answer · answered Jul 05 '11 at 06:35

3

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

answered Jul 05 '11 at 06:35

Jason Goemaat

28,692
15
86
113

Yeah, this is what I've been searching for the last hour - thanks dude. Really pratical. – Arthur Kushman Jul 05 '11 at 06:56

score 1 · Answer 2 · answered Jul 05 '11 at 06:30

1

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

answered Jul 05 '11 at 06:30

sudheshna

1,150
1
13
24

Thanks, but I need a simpliest solution without PROCEDURE and just as I asked on the cars table example, because with the different flows I'll be set the different values and where clauses ans so on. – Arthur Kushman Jul 05 '11 at 06:39
Unlike MySQL, SQL Server does not support the LIMIT clause, also for paging you have to user either ROW_NUMBER, temporary table or with three nested queries. And none of this is not simple as the limit statement in mysql – sudheshna Jul 05 '11 at 06:47
@sudheshna - you're not using @PageSize in your procedure. How well does this perform when taking a small page from a large resultset? – Will A Jul 05 '11 at 06:48

score 0 · Answer 3 · answered Jul 05 '11 at 06:32

-- 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

score 0 · Answer 4 · edited May 23 '17 at 12:23

0

Try this:

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

mysql limit x, y equivalent for SQL Server l?

4 Answers4

Linked