Categories: Window

LEAD

Returns the row after the current one in the same result set without the need for a self-join. If there are no rows, this function returns NULL.

Syntax

LEAD(expression, [offset]) OVER ([PARTITION BY partition_expression] [ORDER BY order_expression]) → same as input type

• expression: An expression that is returned.
• offset: An optional parameter. The number of rows after the current row from which to obtain a value. Supports only a value of 1.
• partition_expression: An optional expression that groups rows into partitions.
• order_expression: An optional expression that specifies the order of the rows within each partition.

Examples

SELECT "Category", 
  "Descript", 
  "DayOfWeek",
  LEAD(DayOfWeek, 1) 
    OVER (
      PARTITION BY "Category" 
      ORDER BY "DayOfWeek")
FROM Samples."samples.dremio.com"."SF_incidents2016.json"

-- Category, Descript, DayOfWeek, EXPR$3
-- ARSON, ARSON, Friday, Friday 
-- ARSON, ARSON OF A VEHICLE, Wednesday, null

Usage Notes

The example uses Dremio’s Samples Data Lake, which includes sample datasets that you can practice with. You must manually add this data lake. To add in Dremio Cloud, see Add Dremio’s Samples Data Lake to Your Project. Guidance to add in Dremio Software is in development.