First_Value Function
Purpose: It returns the first value from the order set of values.
Syntax: First_Value(expression) 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.
Before going to look into some examples of First_Value we will first create the test environment
Run the below script for doing so
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
[MatchID] [int] IDENTITY(1,1) NOT NULL,
[MatchGroup] [varchar](8) NULL,
[MatchBetween] [varchar](50) NULL,
[ScheduleDate] [date] NULL
) ON [PRIMARY]
GO
--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')
-- Project the records
Select * From MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate
1 Group-A India VS Australia 2011-08-14
2 Group-A India VS Pakistan 2011-08-15
3 Group-A India VS Newzealand 2011-08-16
4 Group-A Australia VS Pakistan 2011-08-17
5 Group-A Australia VS Newzealand 2011-08-18
6 Group-A Newzealand VS Pakistan 2011-08-19
7 Group-B USA VS WestIndies 2011-08-20
8 Group-B USA VS Ireland 2011-08-21
9 Group-B USA VS Bangaladesh 2011-08-22
10 Group-B WestIndies VS Ireland 2011-08-23
11 Group-B WestIndies VS Bangaladesh2011-08-24
12 Group-B Ireland VS Bangaladesh 2011-08-25
*/
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
Select
MatchBetween
,ScheduleDate
,First_Value(MatchBetween) Over(Order By ScheduleDate Desc) As [Last Match]
,First_Value(MatchBetween) Over(Order By ScheduleDate ) As [First Match]
From MatchTable
/* Result
MatchBetween ScheduleDate Last Match First Match
India VS Australia 2011-08-14 Ireland VS Bangaladesh India VS Australia
India VS Pakistan 2011-08-15 Ireland VS Bangaladesh India VS Australia
India VS Newzealand 2011-08-16 Ireland VS Bangaladesh India VS Australia
Australia VS Pakistan 2011-08-17 Ireland VS Bangaladesh India VS Australia
Australia VS Newzealand 2011-08-18 Ireland VS Bangaladesh India VS Australia
Newzealand VS Pakistan 2011-08-19 Ireland VS Bangaladesh India VS Australia
USA VS WestIndies 2011-08-20 Ireland VS Bangaladesh India VS Australia
USA VS Ireland 2011-08-21 Ireland VS Bangaladesh India VS Australia
USA VS Bangaladesh 2011-08-22 Ireland VS Bangaladesh India VS Australia
WestIndies VS Ireland 2011-08-23 Ireland VS Bangaladesh India VS Australia
WestIndies VS Bangaladesh2011-08-24 Ireland VS Bangaladesh India VS Australia
Ireland VS Bangaladesh 2011-08-25 Ireland VS Bangaladesh India VS Australia
*/
Well, by looking into the result set for this anyone can argue that, it can be achieved by
a)Row_Number approach as
;With Cte AS(
Select
RowID = ROW_NUMBER() Over(Order by (select 1))
,MatchBetween
,ScheduleDate
From MatchTable )
Select
MatchBetween
,ScheduleDate
, [Last Match] = (Select MatchBetween from Cte where RowID = 12)
, [First Match] = (Select MatchBetween from Cte where RowID = 1)
from Cte
N.B.~ I have deliberately used Row_Number() function instead of the [MatchID] column just to get the work done using Row_Number() way.
b)Max/Min approach as
Select
MatchBetween
,ScheduleDate
, [Last Match] = (Select MatchBetween from MatchTable where ScheduleDate = (Select MAX(ScheduleDate) from MatchTable))
, [First Match] = (Select MatchBetween from MatchTable where ScheduleDate = (Select MIN(ScheduleDate) from MatchTable))
from MatchTable
And this argument will be valid. But wait, is it the only purpose of First_Value() function? Let’s see some more example.
Example 2: Simple First_Value demo with Partition By clause
Select
MatchBetween
,ScheduleDate
,MatchGroup
,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate Desc) As LastMatch
,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate) As FirstMatch
From MatchTable
/* Result
MatchBetween ScheduleDate MatchGroup LastMatch FirstMatch
India VS Australia 2011-08-14 Group-A Newzealand VS Pakistan India VS Australia
India VS Pakistan 2011-08-15 Group-A Newzealand VS Pakistan India VS Australia
India VS Newzealand 2011-08-16 Group-A Newzealand VS Pakistan India VS Australia
Australia VS Pakistan 2011-08-17 Group-A Newzealand VS Pakistan India VS Australia
Australia VS Newzealand 2011-08-18 Group-A Newzealand VS Pakistan India VS Australia
Newzealand VS Pakistan 2011-08-19 Group-A Newzealand VS Pakistan India VS Australia
USA VS WestIndies 2011-08-20 Group-B Ireland VS Bangaladesh USA VS WestIndies
USA VS Ireland 2011-08-21 Group-B Ireland VS Bangaladesh USA VS WestIndies
USA VS Bangaladesh 2011-08-22 Group-B Ireland VS Bangaladesh USA VS WestIndies
WestIndies VS Ireland 2011-08-23 Group-B Ireland VS Bangaladesh USA VS WestIndies
WestIndies VS Bangaladesh2011-08-24 Group-B Ireland VS Bangaladesh USA VS WestIndies
Ireland VS Bangaladesh 2011-08-25 Group-B Ireland VS Bangaladesh USA VS WestIndies
*/
As can be make out that, in this case the function is working based on the partitions made.
Example 3: First_Value with the enhanced over clause
We have already covered Over clause enhancement.It's time to now see it's effect with the First_Value
Select
MatchBetween
,ScheduleDate
,First_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPrec
,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPrec
,First_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr
,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr
,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Current Row) As Rng_UPrec_Curr
,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Current Row) As Row_UPrec_Curr
,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Unbounded Following) As Rng_UPrec_UFoll
,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Unbounded Following) As Row_UPrec_UFoll
,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbounded Following) As Rng_Curr_UFoll
,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounded Following) As Row_Curr_UFoll
From MatchTable
/* Result
MatchBetween ScheduleDate Rng_UPrec Row_UPrec Rng_Curr Row_Curr Rng_UPrec_Curr Row_UPrec_Curr Rng_UPrec_UFoll Row_UPrec_UFoll Rng_Curr_UFoll Row_Curr_UFoll
Ireland VS Bangaladesh 2011-08-25 India VS Australia India VS Australia Ireland VS Bangaladesh Ireland VS Bangaladesh India VS Australia India VS Australia India VS Australia India VS Australia Ireland VS Bangaladesh Ireland VS Bangaladesh
WestIndies VS Bangaladesh 2011-08-24 India VS Australia India VS Australia WestIndies VS Bangaladesh WestIndies VS Bangaladesh India VS Australia India VS Australia India VS Australia India VS Australia WestIndies VS Bangaladesh WestIndies VS Bangaladesh
WestIndies VS Ireland 2011-08-23 India VS Australia India VS Australia WestIndies VS Ireland WestIndies VS Ireland India VS Australia India VS Australia India VS Australia India VS Australia WestIndies VS Ireland WestIndies VS Ireland
USA VS Bangaladesh 2011-08-22 India VS Australia India VS Australia USA VS Bangaladesh USA VS Bangaladesh India VS Australia India VS Australia India VS Australia India VS Australia USA VS Bangaladesh USA VS Bangaladesh
USA VS Ireland 2011-08-21 India VS Australia India VS Australia USA VS Ireland USA VS Ireland India VS Australia India VS Australia India VS Australia India VS Australia USA VS Ireland USA VS Ireland
USA VS WestIndies 2011-08-20 India VS Australia India VS Australia USA VS WestIndies USA VS WestIndies India VS Australia India VS Australia India VS Australia India VS Australia USA VS WestIndies USA VS WestIndies
Newzealand VS Pakistan 2011-08-19 India VS Australia India VS Australia Newzealand VS Pakistan Newzealand VS Pakistan India VS Australia India VS Australia India VS Australia India VS Australia Newzealand VS Pakistan Newzealand VS Pakistan
Australia VS Newzealand 2011-08-18 India VS Australia India VS Australia Australia VS Newzealand Australia VS Newzealand India VS Australia India VS Australia India VS Australia India VS Australia Australia VS Newzealand Australia VS Newzealand
Australia VS Pakistan 2011-08-17 India VS Australia India VS Australia Australia VS Pakistan Australia VS Pakistan India VS Australia India VS Australia India VS Australia India VS Australia Australia VS Pakistan Australia VS Pakistan
India VS Newzealand 2011-08-16 India VS Australia India VS Australia India VS Newzealand India VS Newzealand India VS Australia India VS Australia India VS Australia India VS Australia India VS Newzealand India VS Newzealand
India VS Pakistan 2011-08-15 India VS Australia India VS Australia India VS Pakistan India VS Pakistan India VS Australia India VS Australia India VS Australia India VS Australia India VS Pakistan India VS Pakistan
India VS Australia 2011-08-14 India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia
*/
Hope this helps