Let Us Learn Oracle - Part 35 of N [ Lead function of Oracle ]
Purpose: It is an analytic function.This function returns result set starting from the next row in the table. This function is available since Oracle 8i.
Syntax: Lead(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)
Where,
Expression => A table column or built-in function but not analytical functions
Offset => It is optional and represents the physical offset from the current row in the table. If not specified, the default value is 1 and cannot accept negative numbers.
Default = > It is again optional. If not specified, then whenever the offset value goes out of the table bounds, then default null is returned.
Partition_By_clause = > Partition the query result set. It is again optional
Order By Clause = > Indicates how the data is ordered within the partition.
Example 1: Find next row result/Skip first row
Suppose we want to see the next match date and the next match between the teams. We can do this easily using the Lead function as under
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween) Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Pakistan 15-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Newzealand 16-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Pakistan 17-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Newzealand 18-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Newzealand VS Pakistan 19-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 USA VS WestIndies 20-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 USA VS Ireland 21-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS Bangaladesh 22-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 WestIndies VS Ireland 23-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 Ireland VS Bangaladesh 25-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
The same can also be achieved by using the below query
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,Lead (MatchBetween,1) Over(Order by ScheduleDate) NextMatchBetween
,Lead (ScheduleDate,1) Over(Order by ScheduleDate) NextMatchDate
From MatchTable;
Now let us try to understand the behavior of the Lead function. We have not specified any offset in this query and hence the default value of 1 has been taken into granted. Henceforth, it started from the second row . However, if we explicitly specify the offset value as 1, it will return the same result.
Example 2: Find next to next row result/Skip first 2 rows
If we need to skip 2 rows, we need to specify 2 in the offset as under
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,2) Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,2) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable ;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Newzealand 16-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 Australia VS Pakistan 17-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Newzealand 18-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Newzealand VS Pakistan 19-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 USA VS WestIndies 20-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 USA VS Ireland 21-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 USA VS Bangaladesh 22-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 WestIndies VS Ireland 23-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 Ireland VS Bangaladesh 25-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
Example 3: Specifying 0 in offset
If we give 0 in offset, indicates that we are not skipping any row
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,0) Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,0) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Australia 14-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Pakistan 15-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 India VS Newzealand 16-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Pakistan 17-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Australia VS Newzealand 18-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 Newzealand VS Pakistan 19-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 USA VS WestIndies 20-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS Ireland 21-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 USA VS Bangaladesh 22-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 WestIndies VS Ireland 23-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11 Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
Example 4: Specifying negative offset
Lead function cannot accept negative offset
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,-1) Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,-1) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable ;
,Lead (ScheduleDate,-1) Over(Order by ScheduleDate) NextMatchDate
*
ERROR at line 7:
ORA-01428: argument '-1' is out of range
Example 5: Specifying null value or Space in offset yields null
Giving null value in offset yields null
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,null) Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,null) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable ;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11
2 Group-A India VS Pakistan 15-AUG-11
3 Group-A India VS Newzealand 16-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11
8 Group-B USA VS Ireland 21-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
The above can also hapen if we specify space
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,'') Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,'') Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable ;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11
2 Group-A India VS Pakistan 15-AUG-11
3 Group-A India VS Newzealand 16-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11
8 Group-B USA VS Ireland 21-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
Example 6: Working Lead function with fractional parts
We can even use fractions in the offset clause of Lead function.Let's see the below
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,12/10) Over(Order by ScheduleDate)NextMatchBetween
7 ,Lead (ScheduleDate,12/10) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Pakistan 15-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Newzealand 16-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Pakistan 17-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Newzealand 18-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Newzealand VS Pakistan 19-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 USA VS WestIndies 20-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 USA VS Ireland 21-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS Bangaladesh 22-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 WestIndies VS Ireland 23-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 Ireland VS Bangaladesh 25-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
Example 7: Working with Scalar Expression or Function
We can even use a saclar expression or function in the expression field as
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead ((Select MatchGroup From MatchTable Where MatchID = 1),1) Over(Order by ScheduleDate) NextMatchBetween
7 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHB
---------- ---------- -------------------------------------------------- --------- ----------
1 Group-A India VS Australia 14-AUG-11 Group-A
2 Group-A India VS Pakistan 15-AUG-11 Group-A
3 Group-A India VS Newzealand 16-AUG-11 Group-A
4 Group-A Australia VS Pakistan 17-AUG-11 Group-A
5 Group-A Australia VS Newzealand 18-AUG-11 Group-A
6 Group-A Newzealand VS Pakistan 19-AUG-11 Group-A
7 Group-B USA VS WestIndies 20-AUG-11 Group-A
8 Group-B USA VS Ireland 21-AUG-11 Group-A
9 Group-B USA VS Bangaladesh 22-AUG-11 Group-A
10 Group-B WestIndies VS Ireland 23-AUG-11 Group-A
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 Group-A
12 Group-B Ireland VS Bangaladesh 25-AUG-11
12 rows selected.
Example 8: Using the Default Value of Lead function
This is also an optional argument.If not specified, then the implicit NULL value will be taken into account as the default which we have already seen in the previous examples. However, we can specify our own default value.
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,1) Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Pakistan 15-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Newzealand 16-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Pakistan 17-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Newzealand 18-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Newzealand VS Pakistan 19-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 USA VS WestIndies 20-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 USA VS Ireland 21-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS Bangaladesh 22-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 WestIndies VS Ireland 23-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 Ireland VS Bangaladesh 25-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11 No more Match
12 rows selected.
However, the default value's datatype should be such that it can be converted by the engine as per the datatype of the scalar expression. Else it will throw error
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,1,'No more Match') Over(Order by ScheduleDate) NextMatchDate
8 From MatchTable;
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
no rows selected
Example 9: Working with Partition by clause
It can work smoothly in conjunction with partition by clause as under
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,1,'No more Match in this group') Over(Partition By MatchGroup Order by Sch
eduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,1) Over(Partition By MatchGroup Order by ScheduleDate) NextMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Pakistan 15-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Newzealand 16-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Pakistan 17-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Newzealand 18-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Newzealand VS Pakistan 19-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 No more Match in this group
7 Group-B USA VS WestIndies 20-AUG-11 USA VS Ireland 21-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS Bangaladesh 22-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 WestIndies VS Ireland 23-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 Ireland VS Bangaladesh 25-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11 No more Match in this group
12 rows selected.
Example 10: Lead demonstration with partition by clause to restricted rows
We can even restrict rows as under
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lead (MatchBetween,1,'No more Match in this group') Over(Partition By MatchGroup Order by ScheduleDate) NextMatchBetween
7 ,Lead (ScheduleDate,1) Over(Partition By MatchGroup Order by ScheduleDate) NextMatchDate
8 From MatchTable
9 Where MatchGroup = 'Group-A';
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED NEXTMATCHBETWEEN NEXTMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11 India VS Pakistan 15-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Newzealand 16-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 Australia VS Pakistan 17-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 Australia VS Newzealand 18-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Newzealand VS Pakistan 19-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 No more Match in this group
6 rows selected.
Prior to Denali CTP3, there was no inbuilt Lead function.But we have this now.You can read about Lead of Sql Server from here
Hope you have enjoyed it.Thanks for reading