Open In App

How to find first value from any table in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads