CODEWITHSUNDEEP
sqlcommon-table-expressionrow-number
Kazuo
Kazuo

Reputation: 3

Why can't I use the row number on my where statement?

In the following CTE, outside it, I try to use the RowNum, but it is not recognized.

;with cte as(
select *
,min([Close]) OVER(PARTITION BY YEAR(Date),Crypto) AS 'Min'
,max([Close]) OVER(PARTITION BY YEAR(Date),Crypto) AS 'Max'
from Crypto
)
select [Crypto], [Date],
CASE (min([Close]) OVER(PARTITION BY YEAR(Date),Crypto)) WHEN [Close] 
THEN (min([Close]) OVER(PARTITION BY YEAR(Date),Crypto)) END as Min,
CASE (max([Close]) OVER(PARTITION BY YEAR(Date),Crypto)) WHEN [Close] 
THEN (max([Close]) OVER(PARTITION BY YEAR(Date),Crypto)) END as Max,
RowNum  = row_number() OVER(PARTITION BY YEAR(Date),Crypto, 'Min' ORDER BY YEAR(Date) desc)
from cte
where  ([Close] = Min  or [Close] = Max) and TowNum < 3

I'm not sure where is the problem.

Upvotes: -1

Views: 67

Answers (1)

Doraemon
Doraemon

Reputation: 21

where  ([Close] = Min  or [Close] = Max) and TowNum < 3

I think you misspelled 'RowNum' as 'TowNum' in the last line.

You are correct, it is misspelled, but it still does not work after I corrected it.

Upvotes: -1

Related Questions