title: How do I write paging/limits into a SQL query for 2008 R2?
body: <p>Here is my starting point:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>I don't understand why Microsoft made this so effing difficult.  In MySQL you can just use the LIMIT clause with an offset.</p>
  
  <p>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.  </p>
  
  <p>I'm using SQL 2008 R2.</p>
  
  <p>I've looked at this:
  <a href="http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/" rel="nofollow">http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/</a>, but I can't wrap my mind around how this works.</p>
  
  <p><strong>Here is my attempt based on the above link:</strong></p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>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'.
  </code></pre>