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