What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server

Question

What is the Equivalent syntax of MySQL " LIMIT " clause in SQL Server . I would like to use it for doing paging of my results. (want to show records5 to 10 )

Herculaneum

See also #217173 – Psittacosis

Possible duplicate of How to implement LIMIT with Microsoft SQL Server? – Never

Answer 1

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
  )
)

Ahriman

Answer 2

The closest to it is SELECT TOP X but it is only equivalent to LIMIT X.

For LIMIT X, Y, there is no direct MS-SQL equivalent (as far as I know). Christian's solution is a good one though.

MSSQL2005 (onwards) has the ROW_NUMBER syntax which might be useful:
http://msdn.microsoft.com/en-us/library/ms186734%28SQL.90%29.aspx

Freezer

I really wonder why microsoft doesnt add this feature into sql server? – Hexahydrate

Answer 3

cont=until desired number is starting to get results limit=Want to see how many variables

SELECT TOP (limit) cve_persona FROM persona WHERE (cve_persona > cont)

Skutchan