Filter records in SQL Server

Question

I'm trying to limit the results of a SQL Server query.

I've got this query

SELECT  Name
FROM    MEMB_INFO
LEFT JOIN Character ON Character.AccountID = MEMB_INFO.memb___id
WHERE   CtlCode <> 8
        AND CtlCode <> 32
ORDER BY Name

I want to paginate it. I've found that using

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY Name ) AS rownum
          FROM      Character
        ) AS A
WHERE   A.rownum BETWEEN 10 AND 20

works, but I've got no idea how to add the LEFT JOIN and the WHERE to this query.

I'm using SQL Server 2010

Any ideas? Thanks in advance

Answer 1

If you are using SQL Server 2012, then you can use OFFSET and FETCH keywords, just like:

SELECT  Name
FROM MEMB_INFO A
LEFT JOIN (
    SELECT *
    FROM Character
    ORDER BY Name
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY
) B
ON B.AccountID = A.memb___id
WHERE   CtlCode not in (8,32)
ORDER BY NAME

Then to paginate, you just need to take the number of page starting from zero and multiply by the number defined in OFFSET (number of pages to jump). The size of page is what is defined in FETCH.

Find out more here

Answer 2

SELECT * 
FROM (        
      SELECT  [Character].Name 
             ,row_number() OVER (ORDER BY [Character].Name) AS rownum
      FROM MEMB_INFO LEFT JOIN [Character] 
       ON [Character].AccountID = MEMB_INFO.memb___id 
      AND CtlCode <> 8 AND CtlCode <> 32 
     ) AS A
WHERE A.rownum  BETWEEN 10 AND 20