Can I use LIMIT N,M on SQL Server database

Question

I have the following SQL statement:

SELECT [id], [name]
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
ORDER BY t2.[iid] ASC;

This seems very simple, but I can't figure it out. I need to add LIMIT N,M to it to retrieve M items from the N'th one, but I keep getting errors around 'limit' word. I tried putting that LIMIT clause everywhere I could inside the sql statement above with no avail.

PS. I'm writing for SQL Server that comes with VS2010.

see: http://stackoverflow.com/questions/2840/paging-sql-server-2005-results — Sam Saffron, Nov 11 '11 at 01:13
http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging — Sam Saffron, Nov 11 '11 at 01:13
http://stackoverflow.com/questions/4358253/sql-server-2008-paging-methods — Sam Saffron, Nov 11 '11 at 01:14
if you are willing to wait for denali you get https://sqlblog.org/2010/11/10/sql-server-v-next-denali-using-the-offset-clause-paging — Sam Saffron, Nov 11 '11 at 01:15
Oh wow. N and M will be dynamic values that will be put into this SQL as numbers by ASP.NET script. — ahmd0, Nov 11 '11 at 01:20

Adam Wenger · Accepted Answer · 2011-11-11T01:29:05.367

4

To answer for your query, you may want: (depending on your values for M and N)

WITH cte AS
(
   SELECT [id], [name], ROW_NUMBER() OVER (ORDER BY t2.[iid] ASC) AS rowNumber
   FROM [dbo.test_db_002] t1
   LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
)
SELECT [id], [name]
FROM cte
WHERE rowNumber BETWEEN 3 AND 5

Something to watch out for, the values in the between are BETWEEN N AND N + M

Also, here's a link with information about Common Table Expressions which is the WITH cte syntax I used.

edited Nov 11 '11 at 01:29

answered Nov 11 '11 at 01:20

Adam Wenger

17,100
6
52
63

Jesus Christ! No wonder I have no hair left after trying to come up with this myself )) Let me try it out... – ahmd0 Nov 11 '11 at 01:24

score 3 · Answer 2 · answered Nov 11 '11 at 01:13

3

There's no direct equivalent to LIMIT N,M in SQL Server, but you can do something like this:

SELECT * FROM
  (SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM MyTable) a
WHERE row > 5 and row <= 10

See here for some more info: "LIMIT" in SQL Server

answered Nov 11 '11 at 01:13

Chris Fulstow

41,170
10
86
110

Thanks, but how can I use all that "monstrosity" in my query? (Sorry, for calling your code that -- I'm actually referring to SQL Server....) – ahmd0 Nov 11 '11 at 01:22

Icarus · Answer 3 · 2011-11-11T01:23:52.983

1

You could use Row_Number()

example:

select * from 
(
  select cola, colb, row_number() over (order by col1 desc) as row
  from table ) x
where row between value1 and value2

edited Nov 11 '11 at 01:23

answered Nov 11 '11 at 01:10

Icarus

63,293
14
100
115

1

While "use `ROW_NUMBER`" is the correct answer, your example won't work on SQL Server. You need to use a sub-query or CTE, as shown in the other answers. – LukeH Nov 11 '11 at 01:17

score 0 · Answer 4 · answered Feb 13 '12 at 14:11

0

Limit with offset in sql server 2012:

SELECT email FROM myTable
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

//offset - no. of skipped rows

//next - required no. of next rows

answered Feb 13 '12 at 14:11

Somnath Muluk

55,015
38
216
226

Can I use LIMIT N,M on SQL Server database

4 Answers4

Linked