title: Using ROW_NUMBER in CTE
body: <p>I am trying for a CTE with a ROW_NUMBER function.</p>
  
  <p>Query:</p>
  
  <pre><code>with Demo as
  (
  select *, ROW_NUMBER() over (partition by GID, MID order by MMID) as ROWNUMBER  from Table1 where DateEntered &gt; '2015-06-13 00:00:00.000'
  )
  select  * from Demo 
  </code></pre>
  
  <p>Here, the result I get is</p>
  
  <pre><code>GID   MID   ROWNUMBER
  1      1      1
  1      2      1
  1      2      2
  1      2      3 
  2      1      1
  2      2      1
  2      2      2
  2      3      5
  2      4      4
  </code></pre>
  
  <p>Now, I want to get all the rows where combination of GID,MID has max row number value. But a condition here is that for those rows, the combination of GID,MID should also have 1.</p>
  
  <p>In simple terms, get me the rows with max row number value, if that combination of gid,mid has rownumber=1.</p>
  
  <p>The output I desire is</p>
  
  <pre><code>GID   MID   ROWNUMBER
  1      1      1
  1      2      3 
  2      1      1
  2      2      2
  </code></pre>
  
  <p>I hope i did not made it complex. Can anyone pls inform me on how to do this ?</p>