FIRST VALUE Function in SQL Server

FIRST VALUE Function in SQL Server

The FIRST_VALUE() SQL Server analytical function is used to return first value in an ordered set of list. The syntax for FIRST_VALUE() function is as follows,

FIRST_VALUE([scalar_expression])
OVER([partition_by_clause] [order_by_clause] [rows_range_clause])

The scalar_expression is an expression that returns single value. It does not permit other analytic functions. The partition_by_clause divides the returned list into partitions, Otherwise the function treats all the result list as a single group. The order_by_clause specifies which order the result set should be displayed?. The rows_range_clause specifies the range of rows to be returned in a result set.

Lets see this in detail using an example,

We are going to use "tbl_BankDetails" table for our demo.

CREATE TABLE [dbo].[tbl_BankDetails](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[AccNo] [varchar](450) NULL,
[Month] [bigint] NULL,
[Balance] [decimal](18, 0) NULL,
 CONSTRAINT [PK_tbl_BankDetails] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- insert some demo data
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,2,1000);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,3,1050);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,4,1200);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,6,1300);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,8,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,9,1400);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,1,1900);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,2,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,4,1750);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,5,1400);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,7,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,2,1350);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,4,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,6,1040);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,8,1025);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,9,1104);

So our table look like this,

Now, Lets see how to use FIRST_VALUE() function in a SELECT statement:

select *,
FIRST_VALUE(Balance) OVER(Order by Id) as FirstBalance
from tbl_BankDetails

The FIRST_VALUE() function produces the first balance based on the order of the Id. The returned result set is,

If you want to get first balance based on the order of the month your SELECT statement should be like this,

select *,
FIRST_VALUE(Balance) OVER(Order by Month) as FirstBalance
from tbl_BankDetails

The returned result is,

The FirstBalance is the Balance of the first month that you can see in the above result set.

In order to get first balance based on each account number, we need to use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in our OVER statement like below:

select *,
FIRST_VALUE(Balance) OVER(partition by AccNo Order by Id
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 as FirstBalance
from tbl_BankDetails

The partition by divides our result set based on account number and finds the top most balance and returns that as the FirstBalance on each account number.
The returned result is,