cannot use alias in ROW_NUMBER() over in SQL Server?

I have to create a row_number column ordered by a grouped sum, when using sql:

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by billsum DESC) as rn
from Sales group by Sales.Name

It reports error because row_number over cannot parse the "billsum" alias, I have to write:

select Sales.Name, SUM(Sales.Bill) as billsum, ROW_NUMBER() over (order by SUM(Sales.Bill) DESC) as rn
from Sales group by Sales.Name

so here I write SUM(Sales.Bill) twice, is there anyway to use the alia开发者_高级运维s here?

The MSDN docs for the T-SQL OVER clause say:

value_expression cannot refer to expressions or aliases in the select list.

As already stated out by other member you either have to use CTE or SubQuery.

Not only Row_Number() function but in tsql you can not reference alias in same query, so either you have to use one of the mentioned way above or the expression you used in your post. I hope it makes sense!! :)

Possible work-arounds are to use CTE or a subquery:

SELECT Name, billsum, ROW_NUMBER() OVER (ORDER BY billsum DESC) AS rn
FROM 
  ( SELECT Sales.Name, SUM(Sales.Bill) AS billsum 
    FROM Sales
    GROUP BY Sales.Name
  ) tmp

-- Reorder after cutting out qty = 0.
SELECT  *,ROW_NUMBER()  OVER  (partition by claimno ORDER BY itemno) as 'alias name'
from  dbo.OrderCol
where QTY <> 0