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 |
Last Updated :
15 Nov, 2022
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...