SQL Server Partition by custom column name using row_number over partition

pqwbnv8z 于 12个月前发布在其他

关注(0)|答案(1)|浏览(116)

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 ?

sql-server

来源：https://stackoverflow.com/questions/77685920/partition-by-custom-column-name-using-row-number-over-partition

1条答案

按热度按时间

eufgjt7s1#

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

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Partition by custom column name using row_number over partition

1条答案

相关问题

热门标签

最新问答