Limit 10..20 in SQL Server

LIMIT 10..20 in SQL Server

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"

How to implement LIMIT with SQL Server?

Starting SQL SERVER 2005, you can do this...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

or something like this for 2000 and below versions...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

LIMIT command SQL Server

Try changing your query into this:

WITH paging AS (
    SELECT
        ,Character.AccountID
        ,Character.Name
        ,Character.CtlCode
        ,AccountCharacter.Number
        ,AccountCharacter.ID
        ,memb___id
        ,memb_name
        ,memb__pwd2
        ,mail_addr
        ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
    FROM
        Character, 
        AccountCharacter, 
        MEMB_INFO
    WHERE
        Character.AccountID = AccountCharacter.ID
        AND AccountID=memb___id 
        AND AccountCharacter.ID=memb___id 
)
SELECT TOP ({$per_page}) *
FROM paging
WHERE RowNr > {$page} * {$per_page}
ORDER BY RowNr

Note that page 0 is the first page, 1 is the second, etc.

This uses Common Table Expressions introduced in MSSQL 2005, for earlier versions, something like this should probably work (source Efficient Paging (Limit) Query in SQLServer 2000?):

DECLARE @Sort int

SET ROWCOUNT {$page} * {$per_page}
SELECT @Sort = AccountID FROM Character ORDER BY AccountID

SET ROWCOUNT {$per_page}    
SELECT
    ,Character.AccountID
    ,Character.Name
    ,Character.CtlCode
    ,AccountCharacter.Number
    ,AccountCharacter.ID
    ,memb___id
    ,memb_name
    ,memb__pwd2
    ,mail_addr
    ,ROW_NUMBER() OVER (ORDER BY Character.AccountID) AS RowNr
FROM
    Character, 
    AccountCharacter, 
    MEMB_INFO
WHERE
    Character.AccountID > @Sort
    AND Character.AccountID = AccountCharacter.ID
    AND AccountID=memb___id 
    AND AccountCharacter.ID=memb___id
ORDER BY
    Character.AccountID

Equivalent of LIMIT and OFFSET for SQL Server?

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

Incorrect syntax near 'LIMIT' using mssql

You have to use TOP clause instead of LIMIT

SELECT TOP 10 AgentScores.agentID, AgentScores.totalScore, Agents.firstname, Agents.lastname FROM AgentScores INNER JOIN Agents ON AgentScores.AgentId=Agents.Agent_id ORDER BY AgentScores.totalScore DESC

LIMIT on SQL query

Use TOP:

SELECT TOP 10 Id, Firstname, Surname FROM Person WHERE Firstname LIKE ?

---

Related Topics