Partition by custom column name using row_number over partition

Question

I have a custom column on my query:

CASE 
    WHEN Table.Column0 LIKE '%x%' 
        THEN 'A' 
        ELSE 'B' 
END AS 'CustomColumn',

When I try to partition the data in the query using:

ROW_NUMBER() OVER (PARTITION BY Table.Column1, Table.Column2 ORDER BY Table.Column 3 ASC) AS g2

It works perfectly, but I would also like to add the CustomColumn as criteria for partitioning to further split out the data. Even if I try to add it in [], SQL Server simply tells me

Invalid column name 'CustomColumn'

but the column is showing under the correct name in the data preview.

Is there a way to add it in to the row_number() over (partition... statement ?

Answer 1

The name of the custom column is not visible in the SELECT, WHERE, or GROUP BY clause of the query where it's defined (only in ORDER BY). So you need to use a inline subquery or common table expression (CTE), like this

with q as
(
  select *, case when t.Column0 like '%x%' then 'A' else 'B' end as CustomColumn
  from t
)
select *, row_number() over (partition by CustomColumn order by Column1) rn
from q