Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Introduction
LEAD() and LAG()
9. The LEAD(x, y, z) function
FIRST_VALUE(), LAST_VALUE()
Summary

Instruction

Perfect! The last possible type of LEAD(x, y, z) takes three arguments:

SELECT
  Name,
  OpenDate,
  LEAD(OpenDate,2,'2000-01-01') OVER(ORDER BY OpenDate ASC) AS Lead
FROM Website;

The third argument (z) 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(...).