Menu
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 alias here?
317
The row_number() window function can be used without order by in over to arbitrarily assign a unique value to each row.
The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.
The ROW_NUMBER function cannot currently be used in a WHERE clause. Derby does not currently support ORDER BY in subqueries, so there is currently no way to guarantee the order of rows in the SELECT subquery.
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated.
The MSDN docs for the T-SQL OVER clause say:
value_expressioncannot refer to expressions or aliases in the select list.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
