Skip to main content

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,






Comments

Popular posts from this blog

FORCE ORDER in SQL Server

The FORCE ORDER is a query hint it executes the order of the tables exactly specified in a statement. When we use this query hint in a statement it will tell SQL server not to change the order of the joins in the query. Basically, the SQL server rearrange your joins to be in the order that it thinks it will be optimal for your query to execute. Now, Lets see the execution plan without the FORCE ORDER: The above execution plan demonstrates the optimal order of the joins returned by the SQL server. As you can see the order starts from the sales details and goes by bank details to employee details. Suppose if you don't want the SQL server to change the order of the joins in a query you can use FORCE ORDER to stop the default ordering. The syntax for FORCE ORDER query hint is, OPTION ( FORCE ORDER ); Now, Lets see the execution plan with the FORCE ORDER: select * from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on e.Id=b.EmpID inner join tbl_S

How to create comma separated values in SQL server?

Comma Separated Values in SQL Predominantly in reporting, you may gone through a situation where you need to convert a comma separated values into list of rows or to convert a list of rows into single column to display in a report. In this splash reading, you will understand the following: How to convert comma separated data in a column to multiple rows How to convert multiple rows into one comma separated values Using COALESCE function Using STUFF function Convert comma separated data in a column to multiple rows Here we have an Customer table with a list of products bought by each customer, ID CUSTOMER PRODUCTS 1 Stuart Chain Saw,Circular Saw 2 Michael Drill,Hammer 3 Jonathan Sticky Notes,Mouse 4 Nabeel Mobile,Headset Suppose you want to return this as a single table, list of products bought by each customer. we need to create a function that splits our comma separated col

OPTION (MERGE JOIN) in SQL Server

OPTION (MERGE JOIN) in SQL Server The MERGE JOIN query hint is a best available join algorithm in SQL server. It is based on first sorting  both data sets according to the join conditions and then traversing through the sorted data sets and finding matches. The MERGE JOIN itself is very fast, but it can be an expensive choice if sort operations are required. This produces the best optimal execution plan. The syntax is, OPTION ( MERGE JOIN ); Now, Lets see how to use MERGE JOIN with SELECT statement.? select * from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on b.EmpId=e.Id OPTION(MERGE JOIN); The statement returned optimal execution plan is, The MERGE JOIN operator gets a row from each input and compares them. In above statement, it merges all joins and first sorting data sets and then traversing through the sorted data sets and finding matches, if they are equal the rows are returned. If they are not equal, the lower-value row is removed

How to manipulate JSON data in SQL server?

Manipulate JSON data in SQL Server JavaScript Object Notation (JSON) is a lightweight popular data exchangeable format used across modern IoT platforms, web and mobile applications etc. It is a language independent textual data format. JavaScript Object Notation (JSON) is also used for storing unstructured data in log files or NoSQL Databases such as Microsoft Azure Cosmos DB. Many RESTful web services that allow us to store and retrieve JSON formatted texts among different protocols using different Endpoints. The example of JSON text is as follows, [{         "customer":"Michael",         "products":["Watch","Mobile","Books"] }, {         "customer":"Stuart",         "products":["Laptop","Keyboard","Mouse"] } ] In this article you will understand the following, Read JSON data from table Modify JSON data in a table Validate JSON objects 

OPTION Loop Join in SQL Server

The OPTION ( LOOP JOIN ) would enforce LOOP JOIN across all joins in the query. Using the OPTION ( LOOP JOIN ) appears to allows the query optimizer to join the tables using the nested loops in which ever order SQL server decides it is optimal. The OPTION clause must be a last clause in a statement. The syntax is, OPTION ( LOOP JOIN ); Now, Lets see how to use LOOP JOIN with SELECT statement.? select *from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on b.EmpID=e.Id inner join tbl_SalesDetails as s on s.BankId=b.Id OPTION(LOOP JOIN); The statement returned execution plan is, The above statement loops through all the joins that starts from sales details and goes by bank details to employee details in which the order that SQL server thinks it is optimal. This does not follow the order of joins that we specify. Suppose, if you want the SQL server to follow the order of joins that you specify you have to use FORCE ORDER in OPTION clause. Howe