Equivalent of LIMIT in T-SQL

Question

In PHP I'm doing a SQL Server query where I need (LIMIT) type functionality. I already know that TOP is typically used in SQL Server instead of LIMIT (which is MySQL syntax). However, TOP doesn't allow you to set on 'offset' the way LIMIT does. In my query, I will need the 'offset' and 'number of rows' to be dynamic. Example:

LIMIT $startRow, $maxRow

Any thought, or suggestions?

Looks like someone else has had a similar question: http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000 — Mr Griever, Apr 30 '12 at 16:51
possible duplicate of [Equivalent of LIMIT and OFFSET for SQL Server?](http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server) — Nicola Cossu, Apr 30 '12 at 20:38

score 1 · Answer 1 · answered Apr 30 '12 at 16:52

Sql Server 2005 doesn't have the limit syntax you have above, that wasn't added until 2012. You will want to use the OVER clause and common table expressions.

Example From SqlTeam

WITH Members  AS
(
    SELECT  M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY,
            ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber
    FROM    dbo.FORUM_MEMBERS
)
SELECT  RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM    Members
WHERE   RowNumber BETWEEN 1 AND 20
ORDER BY RowNumber ASC;

Equivalent of LIMIT in T-SQL

1 Answers1

Linked