CODEWITHSUNDEEP
sqlsql-serversql-order-bysubquery
B10
B10

Reputation: 173

Cannot reference subquery column by outer order by

The ORDER BY clause below is causing an error. How can I reference the TotalPrice column in ORDER BY clause: 

SELECT * FROM
(

SELECT O.OrderID,
       (SELECT SUM(SubTotal) FROM DB_OrderDetails OD WHERE OD.OrderID = O.OrderID) AS TotalPrice,
       ROW_NUMBER() OVER (ORDER BY TotalPrice) AS RowNum

    FROM DB_Orders O

) Orders

Upvotes: 0

Views: 1566

Answers (2)

Farhan
Farhan

Reputation: 2575

How about this:

select 
    o.orderID,
    sum(od.SubTotal) as TotalPrice,
    row_number() over (order by sum(od.SubTotal)) as RowNum
from DB_Orders o
join DB_OrderDetails od
    on o.OrderID = od.OrderID
group by o.OrderID

Here is the example: SQL Fiddle

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

You cannot reference it by its name in the same sub-query, you nave to do it in the outer query:

SELECT orders.*, 
       ROW_NUMBER() OVER (ORDER BY TotalPrice) AS RowNum FROM
(
    SELECT O.OrderID,
       (SELECT SUM(SubTotal) FROM DB_OrderDetails OD 
        WHERE OD.OrderID = O.OrderID) AS TotalPrice
    FROM DB_Orders O
) Orders

Upvotes: 4

Related Questions