How to implement LIMIT with Microsoft SQL Server?

Question

I have this query with mysql :

select * from table1 LIMIT 10,20

How can I do this with Microsoft sql ?

Answer 1

SELECT TOP 10 * FROM table;

Is the same as

SELECT * FROM table LIMIT 0,10;

Here's an article about implementing Limit in MsSQL Its a nice read, specially the comments.

Answer 2

Clunky, but it'll work.

SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id

MSSQL's omission of a LIMIT clause is criminal, IMO. You shouldn't have to do this kind of kludgy workaround.

Answer 3

In SQL there's no LIMIT keyword exists. If you only need a limited number of rows you should use a TOP keyword which is similar to a LIMIT.