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.

sql lead example of next value
SQL Lead Example of Next Value

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.

sql lead function output
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.

sql days until field
Lead Function Calculating Days Until

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.

lead function output example sql
Lead Function Output
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.

sql lead indicator example

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

sql indicator flag increase decrease using lead
SQL Lead Example of Indicators
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.

sql latest record example
Finding Latest Invoice

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

sql latest record output
Customer Latest Invoice Records