Getting SQL LIMIT syntax error.

Question

_{Monday, May 25, 2009 3:50 PM}

        SelectCommand="SELECT [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] FROM [UserProfiles] ORDER BY [CreateDate] LIMIT 3,5">

Thats my SQL statement, and its giving me an error saying.

The statement was working before the limit was added, so

  SelectCommand="SELECT [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] FROM [UserProfiles] ORDER BY [CreateDate]"

Incorrect syntax near 'LIMIT'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'LIMIT'.

 

I'm pretty new to SQL, and i tried a few things but couldn't fix it, can someone help me out? Thanks

 

All replies (8)

_{Monday, May 25, 2009 4:59 PM ✅Answered}

 Your exact statement would be

select [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] from (select[UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] , row_number() OVER (order by  [CreateDate]) as RowNumber form [UserProfiles]) Derived where RowNumber between 4 and 9

---

_{Monday, May 25, 2009 4:23 PM}

 The syntax you're trying is MySQL syntax, not SQL Server syntax. I think you may want to look at TOP clause of SQL. What exactly LIMIT 3,5 is doing?

 

Try

select top 5  [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] FROM [UserProfiles] ORDER BY [CreateDate]

---

_{Monday, May 25, 2009 4:30 PM}

 Ok, I see what LIMIT does in MySQL

http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

In SQL Server it's a bit tricky. In SQL Server 2005 and up it's quite easy

select * from (select[UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] , row_number() OVER (order by  [CreateDate]) as RowNumber form [UserProfiles]) Derived where RowNumber between 4 and 9

In prior versions it is more complex, let me know which SQL version you're using.

 

 

---

_{Monday, May 25, 2009 4:49 PM}

 i'm using SQL server 2005, and I tested that query out, and it worked perfectly. Thanks a lot.

 However, now i'm getting a RowNumber field displayed, how do i get rid of that?

---

_{Monday, May 25, 2009 4:56 PM}

 Instead of SELECT * name all the fields explicitly you need to select in both statements, e.g.

select field1, field2, etc. from (select field1, field2, etc., row_number()...) Derived

Now, while you're here in the meantime I was doing a bit of research on this problem for earlier versions of SQL Server. You may take a look at this link

http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

I would appreciate others input on this topic - sounds like an interesting problem.

 

---

_{Monday, May 25, 2009 5:20 PM}

 OK, thats great. Thanks for all the help.

---

_{Tuesday, May 17, 2011 3:22 AM}

 Your exact statement would be

select [UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] from (select[UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] , row_number() OVER (order by  [CreateDate]) as RowNumber form [UserProfiles]) Derived where RowNumber between 4 and 9

Hey,

imagine in the scenario above millions of users.... what do you think the above will do to performance? any other solution than row number...

e.g if i have a website with millions of products... and i want to sort by as welll i magine it numbering millions of my items and then bring out 20 from it what will be the cost of the system? or is there no other way its just this...

thanks in advance

---

_{Sunday, May 22, 2011 10:25 PM}

This is a standard way to apply pagination in SQL Server. I don't know of a different way.