0

What's wrong with the following statement? I'm using SQL Server 2008.

use Demo;

SELECT * FROM users
limit 0 , 30

I got: 

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '0'.

That's really weird. I tried Google but didn't find much info.

Wander Nauta
  • 16,681
  • 1
  • 41
  • 60
daisy
  • 21,114
  • 28
  • 118
  • 236

3 Answers3

5

LIMIT is a MySQL keyword. Use the TOP or ROWCOUNT keywords in MS SQL Server.

Note that TOP can accept a variable, e.g. SELECT TOP( @NumberOfRows ) * FROM Foo;

See: How to use LIMIT keyword in SQL Server 2005? (also valid for 2008)

Depending on how LIMIT is used, there is an important difference between LIMIT and TOP (ranges/pages of data versus just capping the number of results). In that case, the MS SQL syntax is more verbose; usually the ROW_NUMBER() function does the trick combined with some simple logic to calculate the values which are valid for the desired page.

Simple Range Selection Example

SELECT * FROM
(
    SELECT 
        ROW_NUMBER() OVER( ORDER BY SomeColumn ASC ) AS RowNumber, 
        AnotherColumn 
    FROM dbo.MyTable
) Q

WHERE RowNumber BETWEEN 20 AND 30; -- these integers can be variables
Community
  • 1
  • 1
Tim M.
  • 52,666
  • 12
  • 118
  • 157
4
select top 30 * from users

SQL Server uses that syntax rather than the limit form.

paxdiablo
  • 814,905
  • 225
  • 1,535
  • 1,899
juergen d
  • 195,137
  • 36
  • 275
  • 343
1

SQL Server doesn't support the limit clause (that is the MySQL and PostgreSQL syntax). You should use top like this:

select top 30 * from users
Mosty Mostacho
  • 41,294
  • 14
  • 93
  • 121