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?
+3
A:
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
2010-05-21 16:34:42
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
2010-05-21 16:42:56
@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
2010-05-21 16:44:34
@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
2010-05-21 16:50:07
+1, you can put the `TOP n` on the select that uses the CTE
KM
2010-05-21 17:30:46
@KM - Granted, although if you are going to use a CTE with ROW_NUMBER, you might as well go all the way.
Thomas
2010-05-21 17:41:33
+2
A:
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.
Daniel DiPaolo
2010-05-21 16:51:09
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.
acidzombie24
2010-05-21 16:57:13