SQL Server 2005 equivalent of OFFSET / FETCH

Question

Possible Duplicate:
LIMIT 10..20 in sqlserver

I tried to use the following code in SQL Server 2005 (it was written in 2012):

SELECT *
FROM  (SELECT TOP (100) PERCENT ID_BADGE,
                                DATE_TRX,
                                CODE_TRX,
                                SEQ_REC
       FROM   omi.DCUTRX_ZERO
       UNION ALL
       SELECT TOP (100) PERCENT ID_BADGE,
                                DATE_TRX,
                                CODE_TRX,
                                SEQ_REC
       FROM   omi.TAHIST_ZERO) OMI
WHERE  ( ID_BADGE = '     ' + @Badge )
       AND ( DATE_TRX BETWEEN @Date AND @Date2 )
       AND ( SEQ_REC IN ( '0', '1000' ) )
       AND ( CODE_TRX IN ( '100', '101' ) ) 
    OFFSET 1 ROWS 
    FETCH NEXT 2 ROWS ONLY

Can anyone tell me the SQL Server 2005 equivalent?

Please see http://www.sqlservercentral.com/articles/T-SQL/66030/ and all of the follow-up discussion http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx — Aaron Bertrand, Jan 10 '13 at 18:12

score 2 · Accepted Answer · answered Jan 10 '13 at 19:00

SELECT  ID_BADGE, DATE_TRX, CODE_TRX, SEQ_REC
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY DATE_TRX) rn
        FROM    (
                SELECT  ID_BADGE, DATE_TRX, CODE_TRX, SEQ_REC
                FROM    omi.DCUTRX_ZERO
                UNION ALL
                SELECT  ID_BADGE, DATE_TRX, CODE_TRX, SEQ_REC
                FROM    omi.TAHIST_ZERO
                ) q
       WHERE    ID_BADGE = '     ' + @Badge
                AND DATE_TRX BETWEEN @Date AND @Date2
                AND SEQ_REC IN ('0', '1000')
                AND CODE_TRX IN ('100', '101')
       ) q
WHERE  rn BETWEEN 2 AND 3
ORDER BY
       DATE_TRX

Note that you need some kind of ORDER BY in SQL Server for ROW_NUMBER to work.

SQL Server 2005 equivalent of OFFSET / FETCH

1 Answers1