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?

Daniel DiPaolo
  • 55,313
  • 14
  • 116
  • 115
  • Not sure I understand the question. You mean making SQL Server understand `LIMIT` instead of `TOP`? – Daniel DiPaolo May 21 '10 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 –  May 21 '10 at 16:44

2 Answers2

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
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • 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. –  May 21 '10 at 16:42
  • @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. – Thomas May 21 '10 at 16:44
  • @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. – Thomas May 21 '10 at 16:50
  • @KM - Granted, although if you are going to use a CTE with ROW_NUMBER, you might as well go all the way. – Thomas May 21 '10 at 17:41
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.

Community
  • 1
  • 1
Daniel DiPaolo
  • 55,313
  • 14
  • 116
  • 115
  • 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. –  May 21 '10 at 16:57