Let Us Learn Oracle - Part 37 of N [ First_Value function of Oracle ]
Purpose: It is an analytic function.It returns the first value from the order set of values.If the first value in the set is null, then the function returns null until we specify IGNORE NULLS.However, if we specify IGNORE NULLS,then First_Value function returns the first non-nullable value else it returns all null values.
Syntax: First_Value(expression [IGNORE NULLS]) Over( [ Partition_By_clause] order by clause [rows_range_clause])
Where,
Expression => A table column or built-in function but not analytical functions.
Rows_range_clause => It helps to further limit the effect of analytical function.
As said, the First_Value function returns the first value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.
Example 1: Simple First_Value demo for getting the first and the last match played
Suppose we need to find out the first and the last match played. We can achieve it as under
SQL> Select
2 MatchBetween
3 ,ScheduleDate
4 ,First_Value(MatchBetween) Over(Order By ScheduleDate Desc) As "Last Match"
5 ,First_Value(MatchBetween) Over(Order By ScheduleDate ) As "First Match"
6 From MatchTable;
MATCHBETWEEN SCHEDULED Last Match First Match
-------------------------------------------------- --------- ---------------------------------------
India VS Australia 14-AUG-11 Ireland VS Bangaladesh India VS Australia
India VS Pakistan 15-AUG-11 Ireland VS Bangaladesh India VS Australia
India VS Newzealand 16-AUG-11 Ireland VS Bangaladesh India VS Australia
Australia VS Pakistan 17-AUG-11 Ireland VS Bangaladesh India VS Australia
Australia VS Newzealand 18-AUG-11 Ireland VS Bangaladesh India VS Australia
Newzealand VS Pakistan 19-AUG-11 Ireland VS Bangaladesh India VS Australia
USA VS WestIndies 20-AUG-11 Ireland VS Bangaladesh India VS Australia
USA VS Ireland 21-AUG-11 Ireland VS Bangaladesh India VS Australia
USA VS Bangaladesh 22-AUG-11 Ireland VS Bangaladesh India VS Australia
WestIndies VS Ireland 23-AUG-11 Ireland VS Bangaladesh India VS Australia
WestIndies VS Bangaladesh 24-AUG-11 Ireland VS Bangaladesh India VS Australia
Ireland VS Bangaladesh 25-AUG-11 Ireland VS Bangaladesh India VS Australia
12 rows selected.
Example 2: Simple First_Value demo with Partition By clause
SQL> Select
2 MatchBetween
3 ,ScheduleDate
4 ,MatchGroup
5 ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate Desc) As LastMatch
6 ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate) As FirstMatch
7 From MatchTable;
MATCHBETWEEN SCHEDULED MATCHGROUP LASTMATCH FIRSTMATCH
-------------------------------------------------- --------- ---------- ----------------------------
Newzealand VS Pakistan 19-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
Australia VS Newzealand 18-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
Australia VS Pakistan 17-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
India VS Newzealand 16-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
India VS Pakistan 15-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
India VS Australia 14-AUG-11 Group-A Newzealand VS Pakistan India VS Australia
Ireland VS Bangaladesh 25-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
WestIndies VS Bangaladesh 24-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
WestIndies VS Ireland 23-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
USA VS Bangaladesh 22-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
USA VS Ireland 21-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
USA VS WestIndies 20-AUG-11 Group-B Ireland VS Bangaladesh USA VS WestIndies
12 rows selected.
Example 3: First_Value with windowing clause
SQL> Select
2 MatchBetween
3 ,ScheduleDate
4 ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPr
ec
5 ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPre
c
6 ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr
7 ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr
8 ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And
Current Row) As Rng_UPrec_Curr
9 ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And
Current Row) As Row_UPrec_Curr
10 ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And
Unbounded Following) As Rng_UPrec_UFoll
11 ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And
Unbounded Following) As Row_UPrec_UFoll
12 ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbound
ed Following) As Rng_Curr_UFoll
13 ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounde
d Following) As Row_Curr_UFoll
14 From MatchTable;
MATCHBETWEEN SCHEDULED RNG_UPREC ROW_UPREC RNG_
-------------------------------------------------- --------- ---------------------------------------
RNG_CURR_UFOLL ROW_CURR_UFOLL
-------------------------------------------------- -------------------------------------------------
India VS Australia 14-AUG-11 India VS Australia India VS Australia Indi
India VS Australia India VS Australia
India VS Pakistan 15-AUG-11 India VS Australia India VS Australia Indi
India VS Pakistan India VS Pakistan
India VS Newzealand 16-AUG-11 India VS Australia India VS Australia Indi
India VS Newzealand India VS Newzealand
Australia VS Pakistan 17-AUG-11 India VS Australia India VS Australia Aust
Australia VS Pakistan Australia VS Pakistan
Australia VS Newzealand 18-AUG-11 India VS Australia India VS Australia Aust
Australia VS Newzealand Australia VS Newzealand
Newzealand VS Pakistan 19-AUG-11 India VS Australia India VS Australia Newz
Newzealand VS Pakistan Newzealand VS Pakistan
USA VS WestIndies 20-AUG-11 India VS Australia India VS Australia USA
USA VS WestIndies USA VS WestIndies
USA VS Ireland 21-AUG-11 India VS Australia India VS Australia USA
USA VS Ireland USA VS Ireland
USA VS Bangaladesh 22-AUG-11 India VS Australia India VS Australia USA
USA VS Bangaladesh USA VS Bangaladesh
WestIndies VS Ireland 23-AUG-11 India VS Australia India VS Australia West
WestIndies VS Ireland WestIndies VS Ireland
WestIndies VS Bangaladesh 24-AUG-11 India VS Australia India VS Australia West
WestIndies VS Bangaladesh WestIndies VS Bangaladesh
Ireland VS Bangaladesh 25-AUG-11 India VS Australia India VS Australia Irel
Ireland VS Bangaladesh Ireland VS Bangaladesh
12 rows selected.
Example 4: First_Value with IGNORE NULL Option
We can specify IGONE NULL optuon for ignoring the null values as shown under
Select
MatchBetween
,ScheduleDate
,First_Value(MatchBetween Ignore Nulls) Over(Order By ScheduleDate Desc) As "Last Match"
,First_Value(MatchBetween Ignore Nulls) Over(Order By ScheduleDate) As "First Match"
From MatchTable;
N.B.~The current version of First_Value that has been launched with Denali does not support IGNORE NULL Option
Prior to Denali CTP3, there was no inbuilt First_Value function.But we have this now.You can read about First_Value of Sql Server from here
Hope you have enjoyed it.Thanks for reading