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

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  September 29, 2017 at 12:41 am

  #323899

  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:

• Eirikur Eiriksson

  SSC Guru

  Points: 182942

  More actions

  September 29, 2017 at 1:01 am

  #1961528

  info 58414 - Friday, September 29, 2017 12:41 AM

  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:

  Use the third parameter of the function
  

• Thom A

  SSC Guru

  Points: 99137

  More actions

  September 29, 2017 at 2:21 am

  #1961546

  With LAG([FieldName],2), I'd also expect the 2nd row to return a NULL as well. The 2 is telling SQL Server to return the result from 2 rows prior. Thus, the 1st and 2nd rows would not have a return. For example, in the below, 1 and 2 but return NULL for the "lagged" column:
  SELECT N, LAG(N,2) OVER (ORDER BY N) AS LagN
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(N);

  Like Eirikur said though, you need to use a the 3rd parameter of the function to provide a default value. This can be a column value, a static value, or an expression (provided the expression doesn't itself contain a Window Function).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 29, 2017 at 6:05 am

  #1961594

  info 58414 - Friday, September 29, 2017 12:41 AM

  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:

  Try this:USE AdventureWorks;
GO

  SELECT
    s.SalesOrderID,
    s.SalesOrderDetailID,
    s.OrderQty,
    LAG(SalesOrderDetailID, 2, SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID ) AS LagValue
FROM Sales.SalesOrderDetail AS s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty;
GO

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)