What is the Equivalent syntax of mysql “ LIMIT ” clause in SQL Server 2008

Question

i know there is no limit x,y in sqlserver instead of it i use:

 select  ROW_NUMBER(),name OVER (ORDER BY name) AS
 myrow from pack  where myrow > 5 and myrow < 10

but it has following error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'myrow'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'myrow'.

any idea?

Edit

i saw What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server but it didn't solve my problem.

possible duplicate of [What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server](http://stackoverflow.com/questions/1736563/what-is-the-equivalent-syntax-of-mysql-limit-clause-in-sql-server) — SLaks, May 27 '13 at 13:47
@SLaks: that dupe is a bad answer to this question and generally — gbn, May 27 '13 at 13:50
@Mahdi_Nine: you were getting there, just need to tweak syntax — gbn, May 27 '13 at 13:52

score 1 · Accepted Answer · answered May 27 '13 at 13:51

1

Try this one (for 2005 and higher) -

SELECT p.name
FROM ( 
    SELECT  
          name
        , myrow = ROW_NUMBER() OVER (ORDER BY name)  
    FROM dbo.pack
) p
WHERE myrow BETWEEN 5 AND 9

Or try this (for 2012) -

SELECT name
FROM dbo.pack
ORDER BY name 
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

answered May 27 '13 at 13:51

Devart

115,199
22
161
180

@Mahdi_Nine, second query only for `MS SQL 2012` – Devart May 27 '13 at 13:59

score 1 · Answer 2 · answered May 27 '13 at 13:51

In SQL Server 2012 there it the OFFSET FETCH syntax

However, for older versions you have to use ROW_NUMBER but in a derived table

select
    name
from
    (
    select
       name,
       ROW_NUMBER() OVER (ORDER BY name) AS myrow
    from
       pack
    ) X
where
   myrow > 5 and myrow <= 10

Don't use 3 nested TOPs as per the suggested answer in the proposed duplicate

What is the Equivalent syntax of mysql “ LIMIT ” clause in SQL Server 2008

2 Answers2