New Lead Function in SQL Server 2012

In this article, I would like to show the new built-in function Lead in SQL Server. The Lead Function returns simply the next Nth row value in an order or accesses data from a subsequent row. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead function. So let's have a look at a practical example of how to use a Lead statement in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

We'll start by walking through a simple LEAD function.

Creating a table in SQL Server

Now we create a table named employee.

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

 

The following is the sample data for the employee table.

 

 

Lead Function

 

The Lead function is an important function for accessing data from a subsequent row in SQL Server. The Lead function accesses data from a subsequent row in the result by the order selected in the OVER clause for each partition specified in the OVER clause.

 

Syntax

The following is the SQL Analytic Lead function syntax:

LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)

Offset: An input parameter is optional. The offset argument determines the number of rows in order that the SQL Engine will read from. If nothing is provided, then the default value 1 will be used in the Lead function.
OVER: Specifies the order of the rows.
ORDER BY: Provide sort order for the records.
Partition By: The Partition by clause is an optional part of the Lead function and if you don't use it all the records of the result-set will be considered as a part of a single record group or a single partition and then ranking functions are applied.

Example

Let us see the following query:

Select *, Lead(EmpName) OVER (ORDER BY EmpName DESC) AS Result  From Employee

Output

Lead Function with Partition By Clause

The Partition by clause is an optional part of the Lead function. The PARTITION BY clause is specified to partition the rows in the result set. The LEAD function is applied to each partition separately and computation restarts for each partition.

Example

Select *, Lead(EmpName) OVER (partition by EmpName ORDER BY EmpName DESC) AS Result  From Employee

 

Output

 

Lead Function with Group By Clause

Example

Select Lead(EmpName) OVER ( ORDER BY EmpName DESC) AS EmployeeName, SUM(EmpSalary) as EmpSalary  From Employee GROUP BY EmpName

HAVING SUM(EmpSalary) < 30000

Output