How to find first value from any table in SQL Server
We could use FIRST_VALUE() in SQL Server to find the first value from any table. FIRST_VALUE() function used in SQL server is a type of window function that results in the first value in an ordered partition of the given data set.
Syntax :
SELECT *, FROM tablename; FIRST_VALUE ( scalar_value ) OVER ( [PARTITION BY partition_value ] ORDER BY sort_value [ASC | DESC] ) AS columnname ;
Syntax descriptions :
- scalar_value – scalar_value is a value examined over the value of the first row in an ordered partition of the provided data set.
- PARTITION BY – PARTITION BY is optional, it differs the rows of the provided data set into the partitions where the FIRST_VALUE() function is used.
- ORDER BY – ORDER BY states the order of the rows in each of the partition where the FIRST_VALUE()function is used.
Examples :
Let us suppose we have a table named ‘geek_demo’:
SELECT TOP 1000 [Name] ,[City], [Year] FROM [geek_demo];
Name | City | Year |
---|---|---|
Ankit | Delhi | 2019 |
Babita | Noida | 2017 |
Chetan | Noida | 2018 |
Deepak | Delhi | 2018 |
Isha | Delhi | 2019 |
Khushi | Noida | 2019 |
Megha | Noida | 2017 |
Parul | Noida | 2017 |
Example-1 :
Find FIRST VALUE without PARTITION BY clause.
To find the first city for the table ‘geek-demo’ use below query:
SELECT [Name], [Year], FIRST_VALUE(City) OVER ( ORDER BY City ASC ) AS First_City FROM geek_demo;
Output :
Name | Year | First_City |
---|---|---|
Babita | 2017 | Noida |
Chetan | 2018 | Noida |
Khushi | 2019 | Noida |
Megha | 2017 | Noida |
Parul | 2017 | Noida |
Deepak | 2018 | Delhi |
Isha | 2019 | Delhi |
Ankit | 2019 | Delhi |
Example-2 :
Find FIRST VALUE with PARTITION BY clause.
To find the first city according to the year for the table ‘geek-demo’ use below query:
SELECT TOP 1000 [Name] , [Year] , FIRST_VALUE(City) OVER ( PARTITION BY Year ORDER BY City ASC ) AS First_City FROM geek_demo;
Output :
Name | Year | First_City |
---|---|---|
Babita | 2017 | Noida |
Megha | 2017 | Noida |
Parul | 2017 | Noida |
Deepak | 2018 | Delhi |
Chetan | 2018 | Delhi |
Isha | 2019 | Delhi |
Ankit | 2019 | Delhi |
Khushi | 2019 | Delhi |