LIMIT 10..20 in SQL Server
I'm trying to do something like :
SELECT * FROM table LIMIT 10,20
SELECT * FROM table LIMIT 10 OFFSET 10
but using SQL Server
The only solution I found 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:
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...
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.
Read more... Read less...
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
If you are using SQL Server 2012+ vote for Martin Smith's answer and use the
FETCH NEXT extensions to
If you are unfortunate enough to be stuck with an earlier version, you could do something like this,
WITH Rows AS ( SELECT ROW_NUMBER() OVER (ORDER BY [dbo].[SomeColumn]) [Row] , * FROM [dbo].[SomeTable] ) SELECT TOP 10 * FROM Rows WHERE Row > 10
I believe is functionaly equivalent to
SELECT * FROM SomeTable LIMIT 10 OFFSET 10 ORDER BY SomeColumn
and the best performing way I know of doing it in TSQL, before MS SQL 2012.
If there are very many rows you may get better performance using a temp table instead of a CTE.
ROW_NUMBER() is the best you can do. It's actually more correct, because the results of a
top clause don't really have meaning without respect to some specific order. But it's still a pain to do.
Update: Sql Server 2012 adds a
limit -like feature via OFFSET and FETCH keywords. This is the ansi-standard approach, as opposed to
LIMIT, which is a non-standard MySql extension.
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.
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.