Row_number and wrapping select ?

Question

I have this code :

Select name, age , row_number() over (partition by name order by age desc) rn from my table

(goal : select the oldest guy among same names)

If I want to select all the row numbered records which is 1 ,I guess I must do :

Select ... from ( above query) where rn=1

But this force me to wrap it with another select.

Is there any other solution instead of wrapping in select ? (cte is also like wrapping)

Answer 1

This isn't generally possible but is in the specific case in your question.

Windowed functions are allowed only in steps 5.1 and 6 in the Logical Query Processing flow chart here (The SELECT and ORDER BY).

SELECT cannot be used to reduce the returned rows. In SQL Server 2008 ORDER BY can only do so in conjunction with TOP (For 2012 OFFSET ... FETCH can filter on the basis of ORDER). As you are only interested in ones where the ROW_NUMBER expression evaluates to 1 in this case you can use

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY row_number() OVER (partition BY name ORDER BY age DESC) 

SQL Fiddle

Following discussion in the comments about how this could be extended to get the TOP 2 per partition. This would be possible

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY CASE
            WHEN row_number() OVER (partition BY name ORDER BY age DESC) <= 2 THEN 0
            ELSE 1
          END 

I personally wouldn't use either of the queries in this answer though. I would just wrap the query in a table expression and filter using WHERE.