Open In App

How to find first value from any table in SQL Server

Last Updated : 15 Nov, 2022
Comments
Improve
Suggest changes
Like Article
Like
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];
NameCityYear
AnkitDelhi2019
BabitaNoida2017
ChetanNoida2018
DeepakDelhi2018
IshaDelhi2019
KhushiNoida2019
MeghaNoida2017
ParulNoida2017

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 :

NameYearFirst_City
Babita2017Noida
Chetan2018Noida
Khushi2019Noida
Megha2017Noida
Parul2017Noida
Deepak2018Delhi
Isha2019Delhi
Ankit2019Delhi

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 :

NameYearFirst_City
Babita2017Noida
Megha2017Noida
Parul2017Noida
Deepak2018Delhi
Chetan2018Delhi
Isha2019Delhi
Ankit2019Delhi
Khushi2019Delhi

Next Article
Article Tags :

Similar Reads