How do I exclude rows with a null value in the derived column?
<p>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 <code>WHERE NextProductID IS NULL</code> because the column is calculated with the LEAD funciton.</p>

<p>Example code:</p>

<pre><code>SELECT BusinessEntityID, ProductID, LEAD(ProductID) OVER(PARTITION BY BusinessEntityID ORDER BY BusinessEntityID) AS NextProductID
FROM Purchasing.ProductVendor
ORDER BY BusinessEntityID, ProductID
</code></pre>

<p>I'm using AdventureWorks2014. Any help would be appreciated!</p>