CODEWITHSUNDEEP
sqlsql-servert-sql
Roland Schaer
Roland Schaer

Reputation: 1668

TSQL invalid where clause in sub query

I have a situation where I need select the current and previous order amount in a single row using a select statement.

Order Table:

Customer Id
OrderId
OrderDate
OrderAmount

Current Select Statement:

SELECT o.OrderId, o.OrderDate, o.OrderAmount, po.OrderAmount
FROM Order o
LEFT JOIN (
   SELECT TOP(1) so.OrderAmount
   FROM Order so
   WHERE so.CustomerId = o.CustomerId and so.OrderId <> o.OrderId
   ORDER BY so.OrderDate DESC
   ) po

The problem is that the "where" clause in the sub query is not allowed. Is there another method for getting this information.

This is actually a simplification of a more complex select (for a view) that requires data for financial reports for the current and previous reporting period.

Upvotes: 2

Views: 410

Answers (4)

Martin Smith
Martin Smith

Reputation: 453028

You would need OUTER APPLY here.

Your WHERE clause doesn't look right though. I've assume OrderDate is unique below in being able to determine "previous" row.

SELECT o.OrderId,
       o.OrderDate,
       o.OrderAmount,
       po.OrderAmount
FROM   [Order] o
       OUTER APPLY(SELECT TOP(1) so.OrderAmount
                   FROM   [Order] so
                   WHERE  so.CustomerId = o.CustomerId
                          AND so.OrderDate < o.OrderDate
                   ORDER  BY so.OrderDate DESC) po

But you may well be better off left joining on ROW_NUMBER

;WITH Ord
     AS (SELECT OrderId,
                OrderDate,
                OrderAmount,
                CustomerId,
                ROW_NUMBER() OVER ( PARTITION BY CustomerId 
                                       ORDER BY OrderDate)  AS RN
         FROM   [Order])
SELECT o.OrderId,
       o.OrderDate,
       o.OrderAmount,
       po.OrderAmount
FROM   Ord o
       LEFT JOIN Ord po
         ON o.CustomerId = po.CustomerId
            AND o.RN = po.RN + 1

Upvotes: 8

syneptody
syneptody

Reputation: 1280

To illustrate the ROW_NUMBER idea 

with TheOrders as
( 
    select  row_number() over(order by orderdate desc) as RowNum,
            o.OrderId as OrderId
    from Orders o
)
select * from TheOrders

Upvotes: 0

MRM
MRM

Reputation: 435

SELECT o.OrderId, o.OrderDate, o.OrderAmount, po.OrderAmount
FROM Order o
LEFT JOIN (
   SELECT TOP(1) so.OrderAmount
   FROM Order so
   ORDER BY so.OrderDate DESC
   ) po on po.OrderId <> o.OrderId

Upvotes: 0

Maess
Maess

Reputation: 4146

enter code hereYour WHERE clause is not valid because the sub select has no context for the o alias.

Did you mean:

ON so.OrderId <> o.OrderId

Upvotes: 0

Related Questions