1

Is there some lib or function i can use to take my basic sql statement and transform the limit statement to a sql server compatible statement?

Improve this question
2
  • Not sure I understand the question. You mean making SQL Server understand LIMIT instead of TOP? Commented May 21, 2010 at 16:34
  • Dan: I need the offset. ex: LIMIT offset, amount. I can easily browse a page by doing LIMIT pageNumber*amount, amount. How do i do that with sqlserver Commented May 21, 2010 at 16:44

2 Answers 2

Reset to default
3

The closest equivalent of MySQL's LIMIT function is the TOP function. So

Select..
From Table
LIMIT 10

In SQL Server this would be:

Select TOP 10 ...
From Table
Order By ...

ADDITION

Per your comments, you are asking about mimicking the offset parameter on LIMIT. You can do that with a CTE in SQL Server 2005+:

With NumberedItems As
    (
    Select ...
        , ROW_NUMBER() OVER ( Order By ... ) As Num
    From Table
    )
Select ...
From NumberedItems
Where Num Between 5 And 20
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I need the offset to. limit 5, 15 would be like top 20 ignoring first 5. -edit- so its not exactly as simple as a find/replace when there are two params.
@acidzombie24 - If you show us the query that you would run in MySQL that you want to run in SQL Server, we can show you how to write the equivalent.
@acidzombie24 - Ah, you want to know about the offset parameter which is a different kettle of fish. I've updated my post to illustrate how you can achieve that using a CTE (and assuming you are using SQL Server 2005+). There is no 1:1 equivalent function to the LIMIT with an offset parameter in SQL Server.
@KM - Granted, although if you are going to use a CTE with ROW_NUMBER, you might as well go all the way.
2

Sounds like you're wanting to use LIMIT's offset functionality for pagination, in which case the SO question "What is the best way to paginate results in MS SQLServer" has a very good accepted answer.

Improve this answer

1 Comment

Ok thats good but i am generating the SQL. The existing code outputs a limit which is easily understood. I need something to convert the sql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.