CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tony M
Tony M

Reputation: 8302

How do I write paging/limits into a SQL query for 2008 R2?

Here is my starting point:

SELECT * FROM crm_main t1
    INNER JOIN crm_group_relationships t2
        ON t1.id = t2.customerid
    OUTER APPLY (
            SELECT TOP 1 t3.timestamp, t3.customerid, t3.comments
            FROM crm_comments t3 
            WHERE t1.id = t3.customerid
            ORDER BY t3.timestamp ASC
        ) t3 
WHERE t1.dealerid = '9999' AND t2.groupid = '251'

I don't understand why Microsoft made this so effing difficult. In MySQL you can just use the LIMIT clause with an offset.

Basically what I'm trying to do is join 3 tables, one of which has to pull only one row (the most recent comment to be able sort by the latest timestamp of the last comment on record). I was able to achieve this but now I'm stuck at trying to write paging into the query.

I'm using SQL 2008 R2.

I've looked at this: http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/, but I can't wrap my mind around how this works.

Here is my attempt based on the above link:

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM crm_main t1
    INNER JOIN crm_group_relationships t2
        ON t1.id = t2.customerid
    OUTER APPLY (
            SELECT TOP 1 t3.timestamp, t3.customerid, t3.comments
            FROM crm_comments t3 
            WHERE t1.id = t3.customerid
            ORDER BY t3.timestamp ASC
        ) t3 
WHERE t1.dealerid = '9999' AND t2.groupid = '251'
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber - 1) * @RowsPerPage) + 1
        AND @RowsPerPage * (@PageNumber)

Output:

Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'id'.
Msg 8156, Level 16, State 1, Line 16
The column 'id' was specified multiple times for 'SOD'.

Upvotes: 0

Views: 152

Answers (1)

arserbin3
arserbin3

Reputation: 6148

Since you're using Server 2008, you can use this excellent example from that link. (formatted to be more readable):

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT SalesOrderDetailID
    ,SalesOrderID
    ,ProductID
FROM (
    SELECT SalesOrderDetailID
        ,SalesOrderID
        ,ProductID
        ,ROW_NUMBER() OVER (
            ORDER BY SalesOrderDetailID
            ) AS RowNum
    FROM Sales.SalesOrderDetail
    ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber - 1) * @RowsPerPage) + 1
        AND @RowsPerPage * (@PageNumber)

This will return the sixth page, of ten records on each page. ROW_NUMBER() basically assigns a temporary Identity column for this query, ordered by SalesOrderDetailID.

You can then select records where row number is between 61-70, for that sixth page.

Hope that makes sense

Working from your added attempt:

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT *
FROM (
    SELECT t1.*
        ,t3.[timestamp]
        ,t3.comments
        ,ROW_NUMBER() OVER (
            ORDER BY t1.id
            ) AS RowNum
    FROM crm_main t1
    INNER JOIN crm_group_relationships t2 ON t1.id = t2.customerid
    OUTER APPLY (
        SELECT TOP 1 t3.[timestamp]
            ,t3.customerid
            ,t3.comments
        FROM crm_comments t3
        WHERE t1.id = t3.customerid
        ORDER BY t3.TIMESTAMP ASC
        ) t3
    WHERE t1.dealerid = '9999'
        AND t2.groupid = '251'
    ) AS x
WHERE x.RowNum BETWEEN ((@PageNumber - 1) * @RowsPerPage) + 1
        AND @RowsPerPage * (@PageNumber)

Upvotes: 3

Related Questions