Michael Reuter Michael Reuter - 7 months ago 8
SQL Question

How do I exclude rows with a null value in the derived column?

I am running a query where I am calculating the next value using the LEAD function. The problem is that I need to remove the rows that contain a NULL in this "next value" column. I can't do a

WHERE NextProductID IS NULL
because the column is calculated with the LEAD funciton.

Example code:

SELECT BusinessEntityID, ProductID, LEAD(ProductID) OVER(PARTITION BY BusinessEntityID ORDER BY BusinessEntityID) AS NextProductID
FROM Purchasing.ProductVendor
ORDER BY BusinessEntityID, ProductID


I'm using AdventureWorks2014. Any help would be appreciated!

Answer

You can wrap your query in another select and perform filtering there:

SELECT data.BusinessEntityID, data.ProductID, data.NextProductID
FROM (
    -- Your original SELECT becomes the "table"
    SELECT BusinessEntityID, ProductID, 
        LEAD(ProductID) OVER(PARTITION BY BusinessEntityID ORDER BY BusinessEntityID) AS NextProductID
    FROM Purchasing.ProductVendor
    ORDER BY BusinessEntityID, ProductID
) data
WHERE data.NextProductID IS NOT NULL   -- perform the filtering you need
Comments