title: Incrementing Row_Number based on change in value of a column
body: <p>I have a sql view which contains data from 3 linked entities (Title > Edition > SKU). The data in this view is used to search on ANY field within the 3 entities. For example, if you specify a condition title.regionid = '14' the view returns 4,000 unique rows (1 per SKU), which belong to 765 unique Editions, which belong to 456 unique Titles. </p>
  
  <p>What I need is to enable paging based on Titles using Row_Number(). So</p>
  
  <pre><code>SELECT * FROM myview WHERE title.regionid = '14' AND Row BETWEEN 0 AND 35
  </code></pre>
  
  <p>The problem is that my Row column needs to count the rows by Title, not by SKU, so from a result set of 4,000 rows, if the first title contains 12 editions and 65 SKUs, the row number for all 65 rows should be 1, because they belong to the same Title. </p>
  
  <p>I cannot use GroupBy because my view contains 40+ columns all of which can be searched on via the WHERE clause.</p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT *
  FROM (
  SELECT row_number() OVER (ORDER BY a.TitleSort ASC) AS Row, a.* 
  FROM (SELECT * FROM v_AdvancedSearch 
  WHERE 
      istitledeleted = 0
      --AND ISBN = '1-4157-5842-5'
      --AND etc
      ) AS a
  ) d
  WHERE 
  Row BETWEEN 0 AND 35
  </code></pre>
  
  <p>In the first page there are 35 rows which only belong to 4 titles, but the Row column counts by row so it stops there, whereas if it counted by Title I would get 387 rows for page 1... How can I accomplish paging in this situation?</p>