Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)' I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below:

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30


select *
from @t as t
where code = (select min(code) from @t where id = t.id)


I want to see only the records with the code equal to a1, b1 and c4. Can over (partition by...) assign 1 to all those and 2 to b2 and c7 codes, etc, so that at the end I could say where row_number = 1 instead of using a sub-query?
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