Rank in Where Clause

Question

Is it possible to use Rank in a Where Clause. Below is the code that I am intending to use

  Select 
   DebtorID
  ,Rank () over (partition by DebtorID order by BalanceDate) as RankBalanceDate
  ,BalanceDate
  ,Balance
  ,UnallocatedBalance
  ,Overdue
From Debtorbalances
Where Rank () Over (Partition by DebtorID order by BalanceDate) = 1

Answer 1

No, it's not possible.

Windowed functions can only appear in the SELECT or ORDER BY clauses.

You can use a CTE or a subquery to do this:

Using a CTE:

with Cte AS(
    Select 
        DebtorID
        ,rank() over (partition by DebtorID order by BalanceDate) as RankBalanceDate
        ,BalanceDate
        ,Balance
        ,UnallocatedBalance
        ,Overdue
    From Debtorbalances
)
select * 
from Cte
where 
    RankBalanceDate = 1

Using a subquery:

select *
from (
    select 
        DebtorID
        ,rank() over (partition by DebtorID order by BalanceDate) as RankBalanceDate
        ,BalanceDate
        ,Balance
        ,UnallocatedBalance
        ,Overdue
    From Debtorbalances
)t
where 
    RankBalanceDate = 1