Introduction
LEAD and LAG
9. Function LEAD(x,y,z)
FIRST_VALUE, LAST_VALUE, NTH_VALUE
Revision

Instruction

Perfect! The last possible type of LEAD takes three arguments:

SELECT
  name,
  opened,
  LEAD(opened,2,'2000-01-01') OVER(ORDER BY opened)
FROM website;

The new (last) argument tells the function what it should return if no matching value is found. Previously, the last rows got NULLs from the function, because there were no "lead" (further) rows for them. Now, you can specify what should be displayed in such cases instead of the default NULL. Here, we show '2000-01-01'. Note that this value must be of the same type as the column itself: if you show dates with LEAD, the last argument must be a date too. You can't show "not available" or 0 instead.

Exercise

Modify the template based on the previous exercise so that it shows -1 instead of NULL if no LEAD value is found.

Stuck? Here's a hint!

Use a third argument in LEAD(...).

Console

Code editor

Result

TableConsole