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 — SLaks
– SLaks, Commented May 27, 2013 at 13:47
@SLaks: that dupe is a bad answer to this question and generally — gbn
– gbn, Commented May 27, 2013 at 13:50
@Mahdi_Nine: you were getting there, just need to tweak syntax — gbn
– gbn, Commented May 27, 2013 at 13:52

gbn · Accepted Answer · 2013-05-27 13:51:35Z

1

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

answered May 27, 2013 at 13:51

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2013-05-27 13:51:33Z

0

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, 2013 at 13:51

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Devart Over a year ago

@Mahdi_Nine, second query only for MS SQL 2012

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest