SQLTeam.com | Weblogs | Forums

Window Function Lag() - No previous Value when 1 row


#1

Hi,

‌LAG function gives us value which was encountered in previous line.
There is no lag value available for 1 row, and 0 (zero) is returned. Is it possible/Is there a way to return the current value (instead of previous) in this case?

Example:‌
‌USE AdventureWorks
GO
SELECT
s.SalesOrderID,
s.SalesOrderDetailID,
s.OrderQty,
LAG(SalesOrderDetailID, 2) OVER (ORDER BY SalesOrderDetailID ) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty
GO

Regards
Nicole :satisfied:


#2

You have 2 options (at least):

LAG(SalesOrderDetailID, 2 , SalesOrderDetailID ) OVER (ORDER BY SalesOrderDetailID ) AS LagValue

or

ISNULL(LAG(SalesOrderDetailID, 2 , NULL ) OVER (ORDER BY SalesOrderDetailID ) ,SalesOrderDetailID ) AS LagValue