SQL LEAD() Function – Getting Next Value

Very similar to the Lag() function that provides a previous record value is the Lead() function which provides the opposite effect. The lead function allows us to obtain the next record of a field based on a specified grouping (or partition). To previously accomplish this without the lead function in SQL, a self table join would be necessary with a bit more complexity.

In the following SQL lead example, we can see the use of this function to retrieve the next invoice date record of a customer.

The function is used in a single field (column) and the format looks like this:

LEAD( <field_name>, offset ) OVER( PARTITION BY <grouping_field> ORDER BY <field_name>) AS NextValue

In the resulting output we can see that our next invoice date column lines up with the next invoice record for a specific customer.

Calculating Days Until

The output result provides us with information of the next invoice date on a specific invoice record of a customer. We can leverage this function to also include a “days until” column.

The resulting output provides us with detailed invoice information on our customers. If the next date does not exist for a record the resulting output is NULL. This also means that our “days until” field will also return NULL. We may default this field to ‘Unknown’, ‘N/A’, or something similar.

Comparing Invoices

Lead functions can be useful when retrieving date records, but can also be used to retrieve other values of importance. For example, we might want to know if our next invoice amount was greater or less than the current invoice. In this case we can create a flag or indicator field.

This gives us a result that we can easily view to know if our customers invoices are going up or down.

Finding The Latest Record

One last very useful purpose of the lead function is to find the latest record of a set. For example, if we use our previous dataset we can include a WHERE clause looking for only where next values are NULL.

The output now only returns the latest invoice date of our customers since that was what our partition is based on.