title: ROW_NUMBER() OVER PARTITION optimization
body: <p>I have the following query:</p>
  
  <pre><code>SELECT *
  FROM
  (
      SELECT *,
          ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Price ASC) as RowNum
      from Offers) r
  where RowNum = 1
  </code></pre>
  
  <p>Offers table contains about 10 million records. But there are only ~4000 distinct codes there. So I need to get the row with the lowest price for each code and there will be only 4000 rows in the result.</p>
  
  <p>I have an Index on (Code, Price) columns with all other columns in INCLUDE statement.</p>
  
  <p>The query runs for 2 minutes. And if I look at the execution plan, I see Index scan with 10M actual rows. So, I guess it scans the whole index to get needed values.</p>
  
  <p>Why MSSQL do the whole index scan? Is it because subquery needs the whole data? How to avoid this scan? Is there a SQL hint to process only the first row in partition?</p>
  
  <p>Is there another way to optimize such query?</p>