convert LIMIT to TOP

Question

in MySQL we use LIMIT and in sql server we must use TOP instead of LIMIT,but we can use LIMIT in the this way:

Limit X, Y which X is the starting point and Y is duration.

now how can i use TOP like that example?

http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver — Mithun Sreedharan, Aug 08 '12 at 12:21
http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000 — Mithun Sreedharan, Aug 08 '12 at 12:22
http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server — Mithun Sreedharan, Aug 08 '12 at 12:23
http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — Mithun Sreedharan, Aug 08 '12 at 12:23

Joe G Joseph · Accepted Answer · 2012-08-08T12:25:45.373

4

you can use ROW_NUMBER() function , with CTE (Common table Expression)

with cte as(
select *,ROW_NUMBER() over (order by col1) as rownumber from <table>)
select * from cte where rownumber between x and y

edited Aug 08 '12 at 12:25

answered Aug 08 '12 at 12:17

Joe G Joseph

@gbn Yeah, my bad. Carry on as you were :) +1 to the answer. – Fluffeh Aug 08 '12 at 12:22
@Fluffeh: The question is - equivalent of limit x,y in sql server – Joe G Joseph Aug 08 '12 at 12:22

score 3 · Answer 2 · edited May 23 '17 at 11:48

3

You can't before SQL Server 2012 which added FETCH..OFFSET

So you need to "page" using ROW_NUMBER function. Examples:

edited May 23 '17 at 11:48

Community

answered Aug 08 '12 at 12:20

gbn

Even in SQL Server 2000 wouldn't this work? `SELECT TOP 5 * FROM (SELECT TOP 10 * FROM tbl ORDER BY id DESC) AS data ORDER BY id ASC` Perhaps not recommended in the case of other options, but at least it exists... – MatBailie Aug 08 '12 at 12:51
1

@Dems: yes. I assumed that folk would be on SS 2005 at least. In 2012 ;-) – gbn Aug 08 '12 at 12:57

2 Answers2