Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
Yes, you could do that (group by name and then join the derived table to the original table). That's how it is usually done in DBMS that do not have window functions. But why not use the window functions? –  ypercube Jan 16 '13 at 8:55
    
@ypercube what ? I do use it ! the problem is how to read the value ( what other options I have , except wrapping in select) –  Royi Namir Jan 16 '13 at 8:55
2  
I mean why wrapping it another select is a problem? –  ypercube Jan 16 '13 at 8:56
    
@ypercube not a problem , Just want to expand my knowledge. –  Royi Namir Jan 16 '13 at 8:57

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you very much –  Royi Namir Jan 16 '13 at 12:33
    
Martin , top(1) is PER partition...right ? ( I did see in the fiddle) but didnt know it is possible. ( just making sure). ALSO , I could also use top(2) which will provide the 2 oldest. so ...where is the catch (you wrote generally...)? –  Royi Namir Jan 16 '13 at 12:34
    
@RoyiNamir - Exactly one row in every partition will have a value of 1 returned by the ROW_NUMBER so yes it will bring back a row for each partition. I haven't checked the plans the wrapping and using WHERE may well have a better plan anyway. –  Martin Smith Jan 16 '13 at 12:36
1  
@RoyiNamir - Ah yes TOP 2 wouldn't work. It only really works for TOP 1. If you knew the number of partitions then TOP (N + 1) WITH TIES would work for TOP 2 but anything beyond that wouldn't be possible. –  Martin Smith Jan 16 '13 at 12:41
1  
@RoyiNamir - Actually have thought of a way. This is only to show it is possible not something that I would ever actually use. –  Martin Smith Jan 16 '13 at 13:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.