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
The following is the sample data for the employee table.
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.
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.
Let us see the following query:
Select *, Lead(EmpName) OVER (ORDER BY EmpName DESC) AS Result From Employee
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.
Select *, Lead(EmpName) OVER (partition by EmpName ORDER BY EmpName DESC) AS Result From Employee
Lead Function with Group By Clause
Select Lead(EmpName) OVER ( ORDER BY EmpName DESC) AS EmployeeName, SUM(EmpSalary) as EmpSalary From Employee GROUP BY EmpName
HAVING SUM(EmpSalary) < 30000