SQL where nested select not null

Question

I have a Customers table with CustomerID and CustomerName.

I then have a Orders table with CustomerID, datetime OrderPlaced and datetime OrderDelivered.

Bearing in mind that not all customers have placed orders, I would like to get a list of CustomerName, OrderPlaced and OrderDelivered but only for customers that have placed orders and whose orders have already been delivered, and only the most recent OrderPlaced per customer.

I started by doing (fully aware that this does not implement the OrderDelivered limitation to it yet, but already not doing what I want):

SELECT CustomerID,
    (SELECT TOP 1 OrderDelivered 
        FROM Orders ORDER BY OrderDelivered DESC) AS OrderDelivered
FROM Customer
WHERE OrderDelivered IS NOT NULL

But already MS SQL doesn't like this, it says that it doesn't know what OrderDelivered is on the WHERE clause.

How can I accomplish this?

Possible duplicate of [How to use alias column name in where clause in SQL Server](https://stackoverflow.com/questions/16161938/how-to-use-alias-column-name-in-where-clause-in-sql-server) — EzLo, Jan 25 '19 at 12:03
`TOP 1 OrderDelivered ORDER BY OrderDelivered ` is equivalent to `MAX(OrderDelivered)`. You don't need a subquery for this, join the tables and group by CustomerID — Panagiotis Kanavos, Jan 25 '19 at 12:05

score 2 · Accepted Answer · answered Jan 25 '19 at 12:07

Personally, I would move your subquery into the FROM and use CROSS APPLY. Then you can far more easily reference the column:

SELECT C.CustomerID,
       O.OrderDelivered
FROM Customer C
     CROSS APPLY (SELECT TOP 1 OrderDelivered 
                  FROM Orders oa
                  WHERE oa.CustomerID = C.CustomerID --Guess column name for orders
                    AND O.OrderDelivered IS NOT NULL
                  ORDER BY O.OrderDelivered DESC) O;

As, however, this is a CROSS APPLY, then the results will already be filtered; so no need for the WHERE.

Gordon Linoff · Answer 2 · 2019-01-25T12:44:26.817

1

If you want the most recent delivered order, then one method uses apply:

select c.*, o.OrderPlaced, o.OrderDelivered
from customer c cross apply
     (select top (1) o.*
      from orders o
      where o.CustomerID = c.CustomerID and 
            o.OrderDelivered is not null
      order by o.OrderPlaced desc
     ) o;

edited Jan 25 '19 at 12:44

answered Jan 25 '19 at 12:04

Gordon Linoff

1,242,037
58
646
786

1

. . . . You need to write `CROSS/OUTER` prior to `apply` otherwise `SQL Server` will throw error. – Yogesh Sharma Jan 25 '19 at 12:06

score 0 · Answer 3 · answered Jan 25 '19 at 13:25

You can achieve this by using the OVER clause (https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql).

DECLARE @customers TABLE (CustomerId INT, CustomerName NVARCHAR(20))
DECLARE @orders TABLE (CustomerId INT, OrderPlaced DATETIME, OrderDelivered DATETIME)

INSERT INTO @customers VALUES
    (1, 'a'),
    (2, 'b')

INSERT INTO @orders VALUES
    (1, '2019-01-01', null),
    (2, '2019-01-03', '2019-02-01'),
    (2, '2019-01-05', null)

SELECT
    c.CustomerName,
     -- Latest OrderPlaced
    FIRST_VALUE(o.OrderPlaced)
        OVER(PARTITION BY c.CustomerId ORDER BY o.OrderPlaced DESC) AS OrderPlaced,
    -- The matching OrderDelivered
    FIRST_VALUE(o.OrderDelivered)
        OVER(PARTITION BY c.CustomerId ORDER BY o.OrderPlaced DESC) AS OrderDelivered
FROM @customers c
INNER JOIN @orders o ON o.CustomerId = c.CustomerId
WHERE o.OrderDelivered IS NOT NULL

SQL where nested select not null

3 Answers3