FIRST_VALUE Function In SQL Server

FIRST VALUE FUNCTION

The FIRST_VALUE() function in SQL Server fetches the initial value from a designated column. It works alongside the ORDER BY clause to determine the sequence and, if necessary or needed, the PARTITION BY clause to create data partitions before retrieving the first value. This function is useful for obtaining the foremost value based on a specified order in a dataset.

• Introduced in SQL Server2012
• It fetches the initial value from a designated column
• ORDER BY Clause is mandatory
• A PARTITION BY Clause can be used

Syntax: FIRST_VALUE(Column_Name) OVER(ORDER BY COLUMN1, COLUMN2, COLUMN3.............).

We will be utilizing the table mentioned below. The provided code is available if you would like to experiment with it.

CREATE TABLE Employees(
ID INT ,
NAME VARCHAR(50),
GENDER VARCHAR(10),
SALARY INT)

INSERT INTO Employees VALUES(
1, 'Joe', 'Male', 10000
),
(
2, 'Ryan', 'Male', 20000
),
(
3, 'Hannah', 'Female', 30000
),(
4, 'Joy', 'Female', 40000
),
(
5, 'Rash', 'Male', 50000
),
(
6, 'Halima', 'Female', 60000
),
(
7, 'Chris', 'Male', 70000
),
(
8, 'Judith', 'Female', 80000
) 

First_Value Function without PARTITION BY

SELECT Name, Gender, Salary,
FIRST_VALUE(Name) over(order by salary) as FirstValue
from Employees

The FIRST_VALUE function in SQL Server operates by specifying the column from which you want to retrieve the initial value. It requires the use of the OVER keyword along with an ORDER BY clause to organize your result set. For instance, we employed this function on a column named 'name' and order it accordingly, selecting 'JOE' as the first value, the function will return 'JOE' for all rows, effectively populating the entire result set with this specific initial value from the 'name' column. Here is the expected result set.

First_Value Function with PARTITION BY

SELECT Name, Gender, Salary,
FIRST_VALUE(Name) over(Partition by Gender order by salary) as FirstValue
from Employees

The FIRST_VALUE function in SQL Server has the flexibility to incorporate the PARTITION BY clause when necessary. Similar to the previous example, it retrieves the initial value from a designated column. However, when used with PARTITION BY, the function divides the result set based on a specified category, like how we partion with 'GENDER', creating separate partitions for analysis or extraction of the initial values within each distinct partition. Here is the expected outcome.

For SQL and Tech related article, let's connect on X and Linkedin. Happy Learning!