An inter-row function that returns the value of an attribute in a subsequent row in the table or table partition.
LEAD (value_expr) [, offset [, default]]) OVER ([PARTITION BY window partition] ORDER BY window ordering)
Parameter |
Description |
|---|---|
value_expr |
Table column or expression defining the offset data to return from the table. |
offset |
The number of rows below the current row, expressed as a non-negative exact numeric literal, or as a SQL variable with exact numeric data. The permitted range is 0 to 231. |
default |
The value to return if the offset value goes beyond the scope of the table or partition. |
window partition |
(Optional) One or more value expressions separated by commas indicating how you want to divide the set of result rows. |
window ordering |
Defines the expressions for sorting rows within window partitions, if specified, or within the result set if you did not specify a window partition. |
The LEAD function requires an OVER (ORDER_BY) window specification. The window partitioning clause in the OVER (ORDER_BY) clause is optional. The OVER (ORDER_BY) clause must not contain a window frame ROWS/RANGE specification.
You cannot define an analytic expression in value_expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr.
You must enter a non-negative numeric data type for offset. Entering 0 returns the current row. Entering a negative number generates an error.
The default value of default is NULL. The data type of default must be implicitly convertible to the data type of the value_expr value or else SAP Sybase IQ generates a conversion error.
The following example returns salary data from the Employees table, partitions the data by department ID, and orders the data according to employee start date. The LEAD function returns the salary from the next row (a physical offset of one row) and displays it under the LEAD (Salary) column:
SELECT DepartmentID dID, StartDate, Salary, LEAD(Salary, 1) OVER(PARTITION BY dID ORDER BY StartDate) FROM Employees ORDER BY 1,2;
The returned result set is:
dID StartDate Salary Lead(Salary) ========= =========== ========== ============= 100 1984-08-28 45,700.000 62,000.000 100 1985-01-01 62,000.000 57,490.000 100 1985-06-17 57,490.000 72,995.000 100 1986-06-07 72,995.000 48,023.690 ... 100 1990-08-19 54,900.000 NULL 200 1985-02-03 38,500.000 39,300.000 200 1987-02-19 39,300.000 49,500.000 200 1987-07-10 49,500.000 54,600.000 200 1988-11-28 46,200.000 34,892.000 200 1989-06-01 34,892.000 87,500.000 ... 200 1993-08-12 47,653.000 NULL