FIRST_VALUE Function - SQL
Overview
The FIRST_VALUE function in SQL returns the first value in an ordered set of values within a partition. It is useful for retrieving the first entry in a specified order for each partition of a result set.
Example:
SELECT department_id, first_name, salary,
FIRST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
FIRST_VALUE(expression) OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
FIRST_VALUE(expression) retrieves the first specified expression value based on the specified order (e.g., by hire date) within each partition.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a FIRST_VALUE column to the first employee within their department based on descending salary order. Richard is the first employee in department_id 1 based on his salary, his name is repeated 3 times since there are 3 results from department_id 1. The same goes for Jane except she is the highest salary in department_id 2.
Example: Query with the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column of employees in department_id 1, along with a FIRST_VALUE column to the first employee within this department based on descending salary order. Richard is the first employee in department_id 1 based on his salary, his name is repeated 3 times since there are 3 results from department_id 1.
