CODEWITHSUNDEEP
sql-server-2008t-sqlsubquerycorrelated-subquery
Ian Warburton
Ian Warburton

Reputation: 15676

Looking for an alternative to a T-SQL Sub-Query

Account
=======
int AccountId PK

Order
=====
int OrderId PK
int AccountId FK
DateTime Date
int Status

For each account I want to know the most recent order that has a status of 1 (Success) otherwise the most recent order that has a status of 0 (Unsuccessful). Date is not unique.

I've got this working with a correlated sub-query in a view like this...

SELECT
    a.AccountId,
    (SELECT TOP 1
            o.orderId
        FROM
            tbl_order o
        WHERE
            o.Accountid = a.AccountId
            AND
            o.Status IN (0, 1)
        ORDER BY
            o.Status DESC, o.Date DESC  
        ) AS OrderId
FROM
    tbl_account a

...but its slow.

Is there a better way?

Upvotes: 0

Views: 2506

Answers (2)

Chandu
Chandu

Reputation: 82913

Try this:

SELECT
    a.AccountId,
        o.OrderId
FROM
    tbl_account a OUTER APPLY 
        (
            SELECT TOP 1 o.orderId
        FROM tbl_order o
       WHERE o.Accountid = a.AccountId
         AND o.Status IN (0, 1)
        ORDER BY o.Status DESC, o.Date DESC  
     ) AS o

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

You could use a CTE with ROW_NUMBER function:

WITH cte AS(
    SELECT  a.AccountId, o.OrderId, o.Date, o.Status
    , RN = ROW_NUMBER()OVER(Partition By a.AccountId ORDER BY o.Status DESC, o.Date DESC) 
    FROM Account a 
    LEFT OUTER JOIN [Order] o  ON a.AccountId = o.AccountId
)
SELECT AccountId, OrderId
FROM cte
WHERE RN = 1

Here's a fiddle: http://sqlfiddle.com/#!3/4e1e3/4/0

Upvotes: 3

Related Questions