CODEWITHSUNDEEP
sql-serverwhere-clausepartition
user1700890
user1700890

Reputation: 7730

Filter with partition over in SQL Server

Here is that I would like to have

select 
    *,  
    rank() over (partition by col_a order by col_b) as some_rank 
from 
    table_A
where 
    some_rank = 1

Obviously, this is not going to work. I can use CTE or temp table to get what I want, but I wonder if can do it all at once (without CTE or temp table). Something like having with group by

Upvotes: 0

Views: 5542

Answers (2)

Daniel N
Daniel N

Reputation: 1208

you can do this old fashioned way

SELECT * 
FROM 
    (
    select 
        *,  
        rank() over (partition by col_a order by col_b) as some_rank 
    from 
        table_A
    ) T
WHERE some_rank = 1

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use top (1) with ties clause :

select top (1) with ties ta.*
from table_A ta
order by rank() over (partition by col_a order by col_b);

Only the sadness is, you can't filter out rows which are greater than 1 & this has some performance downgrades.

Upvotes: 1

Related Questions