SQL Server paging query

pagination, sql, sql-server

Solution

This is how I would do it in SQL Server 2005+:

SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1

Note that I'm using a little integer division trickery to calculate page number.

Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:

    SELECT a.ID, a.Name, b.Photo, c.Created_Date
       , identity(int,1,1) as rowNum
    INTO t
    FROM a
       JOIN b ON a.ID = b.ID
       JOIN c ON c.photo = b.photo
    WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
    ORDER BY c.Created_Date ASC
    GO

    ALTER TABLE t ADD pageNum AS rowNum / 20
    GO

    SELECT ID, Name, Photo, Created_Date, rowNum
    FROM t
    WHERE pageNum = 1

Problem

Urggggg! I've been struggling with this for a long time! I can do it with MySQL so easy but not with SQL Server :( Here are the simplified tables which should be joined all together. Combining all of them by using inner join syntax, I have to write a query to use for paging in the future (btw, PHP). Let's say I need all ID, Name, and Date info which a picture is taken between 2012-10-01 and 2012-10-30.... and 20 people per page. What would be the easiest query to achieve the goal here? (I tried NOT IN.. but it was so buggy cuz I'm not used to 'NOT IN' stuff...) You can ignore the performance speed. Thank you!

Original source