Open In App

How to find last value from any table in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

We could use LAST_VALUE() in SQL Server to find the last value from any table. LAST_VALUE() function used in SQL server is a type of window function that results the last value in an ordered partition of the given data set.

Syntax :

SELECT *,
FROM tablename
LAST_VALUE ( scalar_value )  
OVER (  
  [PARTITION BY partition_expression ]
  ORDER BY sort_expression [ASC | DESC]
) 
AS columname ; 

Where the terminologies description as follows.
scalar_value –
scalar_value is a value analyzed against the value of the last row in an ordered partition of the given data set.

PARTITION BY –
PARTITION BY clause is optional, it differs the rows of the given data set into partitions where the LAST_VALUE() function is used.

ORDER BY –
ORDER BY clause defines the order of the rows in each partition where the LAST_VALUE() function is used.

Example-1 :
Let us suppose we have a table named ‘geekdemo’;

SELECT Name, City, Year
FROM [geekdemo] ;
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-2 :
LAST_VALUE() without PARTITION BY clause :

SELECT TOP 1000 Name, 
Year, LAST_VALUE(City) 
OVER 
(ORDER BY City ASC ) AS Last_City
FROM geekdemo;

Output :

Name Year Last_City
Deepak 2018 Delhi
Isha 2019 Delhi
Ankit 2019 Delhi
Babita 2017 Noida
Chetan 2018 Noida
Khushi 2019 Noida
Megha 2017 Noida
Parul 2017 Noida

Example-3 :
LAST_VALUE() with PARTITION BY clause :

SELECT Name, 
Year, 
LAST_VALUE(City) OVER 
(PARTITION BY Year
ORDER BY City ASC) AS Last_City
FROM geekdemo;

Output :

Name Year Last_City
Babita 2017 Noida
Megha 2017 Noida
Parul 2017 Noida
Deepak 2018 Delhi
Chetan 2018 Noida
Isha 2019 Delhi
Ankit 2019 Delhi
Khushi 2019 Noida

Last Updated : 07 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads