title: RNO's are assigned completely different when running the outer query when using the ROW_NUMBER() OVER(PARTITION BY
body: <pre><code>SELECT  KID,SSN,SID,YEAR,FNAME,LNAME,DOB, GENDER, RACE 
      FROM 
      ( SELECT  KID,SSN, SID,YEAR,FNAME,LNAME,DOB, GENDER, RACE,ROW_NUMBER()  OVER(PARTITION BY FNAME,LNAME,DOB
              ORDER BY year DESC ) RNO    
              from #t17
          )A
              WHERE RNO=1 
  </code></pre>
  
  <p>I am doing Partition on FNAME,LNAME,DOB and I need to get the most recent year record of each person so I am doing order by 'YEAR' column, when I just run the inner query the RNO's are assigned correctly for each record but when I run the outer query, if you can see the outer select and I have kept a condition where RNO=1 then I am getting different records.
  Issue is with the outer query when I run the outer query the RNO's are changing completely and I also tried of keeping inner query in a temp table and then thought of filtering the RNO that is also showing different RNO's if I do so. 
  I have also tried using CTE that is also showing an issue with the RNO when I run the outer query. Please let me know if there is any alternative </p>