title: T-SQL: Paging WITH TIES
body: <p>I am trying to implement a paging routine that's a little different.</p>
  
  <p>For the sake of a simple example, let's assume that I have a table defined and populated as follows:</p>
  
  <pre><code>DECLARE @Temp TABLE
  (
      ParentId    INT,
      [TimeStamp] DATETIME,
      Value       INT
  );
  
  INSERT INTO @Temp VALUES (1, '1/1/2013 00:00', 6);
  INSERT INTO @Temp VALUES (1, '1/1/2013 01:00', 7);
  INSERT INTO @Temp VALUES (1, '1/1/2013 02:00', 8);
  INSERT INTO @Temp VALUES (2, '1/1/2013 00:00', 6);
  INSERT INTO @Temp VALUES (2, '1/1/2013 01:00', 7);
  INSERT INTO @Temp VALUES (2, '1/1/2013 02:00', 8);
  INSERT INTO @Temp VALUES (3, '1/1/2013 00:00', 6);
  INSERT INTO @Temp VALUES (3, '1/1/2013 01:00', 7);
  INSERT INTO @Temp VALUES (3, '1/1/2013 02:00', 8);
  </code></pre>
  
  <p><code>TimeStamp</code> will always be the same interval, e.g. daily data, 1 hour data, 1 minute data, etc. It will not be mixed.</p>
  
  <p>For reporting and presentation purposes, I want to implement paging that:</p>
  
  <ol>
  <li>Orders by <code>TimeStamp</code></li>
  <li>Starts out using a suggested <code>pageSize</code> (say 4), but will automatically adjust to include additional records matching on <code>TimeStamp</code>. In other words, if 1/1/2013 01:00 is included for one <code>ParentId</code>, the suggested <code>pageSize</code> will be overridden and all records for hour 01:00 will be included for all <code>ParentId's</code>. It's almost like the <code>TOP WITH TIES</code> option. </li>
  </ol>
  
  <p>So running this query with <code>pageSize</code> of 4 would return 6 records. There are 3 hour 00:00 and 1 hour <code>01:00</code> by default, but because there are more hour <code>01:00's</code>, the <code>pageSize</code> would be overridden to return all hour <code>00:00</code> and <code>01:00</code>.</p>
  
  <p>Here's what I have so far, and I think I'm close as it works for the first iteration, but sequent queries for the next <code>pageSize+</code> rows doesn't work.</p>
  
  <pre><code>WITH CTE AS
  (
      SELECT ParentId, [TimeStamp], Value,
      RANK() OVER(ORDER BY [TimeStamp]) AS rnk,
      ROW_NUMBER() OVER(ORDER BY [TimeStamp]) AS rownum
      FROM @Temp
  )
  
  SELECT *
  FROM CTE
  WHERE (rownum BETWEEN 1 AND 4) OR (rnk BETWEEN 1 AND 4)
  ORDER BY TimeStamp, ParentId
  </code></pre>
  
  <p>The ROW_NUMBER ensures the minimum pageSize is met, but the RANK will include additional ties.</p>