Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)'
Yes but not with row_number(). You can use either the window aggregate min(): 

select id, code, descriptor
from 
  ( select id, code, descriptor, 
           min_code = min(code) over (partition by id)
    from @t
  ) as t
where code = min_code ;


Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check):

select id, code, descriptor
from 
  ( select id, code, descriptor, 
           rnk = rank() over (partition by id order by code)
    from @t
  ) as t
where rnk = 1 ;




I was about to comment that there is no way to do this without subqueries of any kind (correlated, derived tables, ctes) but alas, there is. Not in 2008R2 but in 2012 version using one more window function. 

Note: Do not use this. The efficiency will probably be the worse. The following query might be good for an obfuscation contest but not for production. 

Instead, check the nice execution plan of the above 2nd query, when there is a suitable index.

Just for fun - and horror of the future maintainer of the code:

select distinct
    id, 
    code = min(code) over (partition by id),
    descriptor = 
        case when code = min(code) over (partition by id)
            then descriptor
            else first_value(descriptor) over 
                    (partition by id order by code, descriptor)
        end
from @t ;


Tested at SQLFiddle