How to implement LIMIT with SQL Server?
Starting SQL SERVER 2005, you can do this...
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 10 AND 20;
or something like this for 2000 and below versions...
SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC
What is the Equivalent syntax of mysql LIMIT clause in SQL Server
The closest thing is TOP:
Select top 5 * from tablename
You can get a range ( rows 5 - 10)
SELECT * FROM (
SELECT TOP n * FROM (
SELECT TOP z columns -- (z=n+skip)
FROM tablename
ORDER BY key ASC
)
)
MySQL LIMIT clause equivalent for SQL SERVER
In SQL Server 2012, there is support for the ANSI standard OFFSET
/ FETCH
syntax. I blogged about this and here is the official doc (this is an extension to ORDER BY
). Your syntax converted for SQL Server 2012 would be:
SELECT ID, Name, Price, Image
FROM Products
ORDER BY ID ASC
OFFSET (@start_from - 1) ROWS -- not sure if you need -1
-- because I don't know how you calculated @start_from
FETCH NEXT @items_on_page ROWS ONLY;
Prior to that, you need to use various workarounds, including the ROW_NUMBER()
method. See this article and the follow-on discussion. If you are not on SQL Server 2012, you can't use standard syntax or MySQL's non-standard LIMIT
but you can use a more verbose solution such as:
;WITH o AS
(
SELECT TOP ((@start_from - 1) + @items_on_page)
-- again, not sure if you need -1 because I
-- don't know how you calculated @start_from
RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC)
/* , other columns */
FROM Products
)
SELECT
RowNum
/* , other columns */
FROM
o
WHERE
RowNum >= @start_from
ORDER BY
RowNum;
There are many other ways to skin this cat, this is unlikely to be the most efficient but syntax-wise is probably simplest. I suggest reviewing the links I posted as well as the duplicate suggestions noted in the comments to the question.
LIMIT 10..20 in SQL Server
The LIMIT
clause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite.
Other brands of database may have similar features (e.g. TOP
in Microsoft SQL Server), but these don't always work identically.
It's hard to use TOP
in Microsoft SQL Server to mimic the LIMIT
clause. There are cases where it just doesn't work.
The solution you showed, using ROW_NUMBER()
is available in Microsoft SQL Server 2005 and later. This is the best solution (for now) that works solely as part of the query.
Another solution is to use TOP
to fetch the first count + offset rows, and then use the API to seek past the first offset rows.
See also:
- "Emulate MySQL LIMIT clause in Microsoft SQL Server 2000"
- "Paging of Large Resultsets in ASP.NET"
Equivalent of LIMIT and OFFSET for SQL Server?
The equivalent of LIMIT
is SET ROWCOUNT
, but if you want generic pagination it's better to write a query like this:
;WITH Results_CTE AS
(
SELECT
Col1, Col2, ...,
ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
FROM Table
WHERE
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit
The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).
Note: the @Offset
parameter should use one-based indexing for this rather than the normal zero-based indexing.
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
this shows the different ways:
-- DB2
select * from table fetch first 10 rows only
-- Informix
select first 10 * from table
-- Microsoft SQL Server and Access
select top 10 * from table
-- MySQL and PostgreSQL
select * from table limit 10
-- Oracle
select * from (select * from table) where rownum <= 10
Related Topics
Rails Query Through Association Limited to Most Recent Record
Delete Duplicate Records from a SQL Table Without a Primary Key
How to Find Which Tables Reference a Given Table in Oracle SQL Developer
How to Include a PHP Variable Inside a MySQL Statement
How to Add Conditional Where Clauses in Rails
How Can Multiple Rows Be Concatenated into One in Oracle Without Creating a Stored Procedure
Postgresql: Which Datatype Should Be Used for Currency
Microsoft Jet Wildcards: Asterisk or Percentage Sign
Concatenate Values Based on Id
How to Assign an Exec Result to a SQL Variable
What Does Include() Do in Linq
How to Create a Step in My SQL Server Agent Job Which Will Run My Ssis Package
Updating Table Rows in Postgres Using Subquery
Convert Excel Date Serial Number to Regular Date
How Many Rows in a Database Are Too Many
How to Split a Single Column Values to Multiple Column Values
Single Fixed Table with Multiple Columns VS Flexible Abstract Tables
Using Isnull VS Using Coalesce for Checking a Specific Condition