How to find last value from any table in SQL Server

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

My Personal Notes arrow_drop_up

Save