LIMIT 10..20 in sqlserver

Question

I'm trying to do something like :

SELECT * FROM table LIMIT 10,20

or

SELECT * FROM table LIMIT 10 OFFSET 10

but using SQLServer

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 MSserver 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...

Answer 1

Unfortunately, the ROW_NUMBER() is the best you can do. It's actually more correct, because the results of a limit or top clause don't really have meaning without respect to some specific order. But it's still a pain to do.

Answer 2

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 row > 5 and row <= 10

Answer 3

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"

Answer 4

please help me i want to fetch 10 records from table but cindition is that i doesn;t want to fetch top 2 records from table all these things will do with order by publish_date field.