LIMIT 10..20 in SQL Server

I'm trying to do something like :

SELECT * FROM table LIMIT 10,20

or

SELECT * FROM table LIMIT 10 OFFSET 10

but using SQL Server

The only [solution I found][1] looks like overkill:

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE row > 5 and row <= 10

I also [found][2]:

SELECT TOP 10 * FROM stuff;

... but it's not what I want to do since I can't specify the starting limit.

Is there another way for me to do that ?

Also, just curious, is there a reason why doesn't SQL Server support the `LIMIT` function or something similar? I don't want to be mean, but that really sounds like something a DBMS needs ... If it does, then I'm sorry for being so ignorant! I've been working with MySQL and SQL+ for the past 5 years so...


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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][1]"
- "[Paging of Large Resultsets in ASP.NET][2]"


[1]:

[To see links please register here]

[2]:

[To see links please register here]

For SQL Server 2012 + [you can use][1].

SELECT *
FROM sys.databases
ORDER BY name
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY

[1]:

[To see links please register here]

SELECT TOP 10 *
FROM TABLE
WHERE IDCOLUMN NOT IN (SELECT TOP 10 IDCOLUMN FROM TABLE)

Should give records 11-20.
Probably not too efficient if incrementing to get further pages, and not sure how it might be affected by ordering.
Might have to specify this in both WHERE statements.

How about this?

SET ROWCOUNT 10

SELECT TOP 20 *
FROM sys.databases
ORDER BY database_id DESC

It gives you the last 10 rows of the first 20 rows. One drawback is that the order is reversed, but, at least it's easy to remember.

as you found, this is the preferred sql server method:

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE a.row > 5 and a.row <= 10

select * from (select id,name,ROW_NUMBER() OVER (ORDER BY id asc) as row
from tableName1) tbl1
where tbl1.row>=10 and tbl1.row<=15

Will print rows from 10 to 15.

So far this format is what is working for me (not the best performance though):

SELECT TOP {desired amount of rows} *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY {order columns} asc)__row__ FROM {table})tmp
WHERE __row__ > {offset row count}

A note on the side, paginating over dynamic data can lead to strange/unexpected results.

From the MS SQL Server online documentation (

[To see links please register here]

), here is their example that I have tested and works, for retrieving a specific set of rows. ROW_NUMBER requires an OVER, but you can order by whatever you like:

WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate, RowNumber
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

A good way is to create a procedure:

create proc pagination (@startfrom int ,@endto int) as
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name desc) as row FROM sys.databases
) a WHERE a.row > @startfrom and a.row <= @endto


just like limit 0,2
///////////////
execute pagination 0,4