Skip to content
Related Articles

Related Articles

Improve Article

How to find last value from any table in SQL Server

  • Last Updated : 07 Dec, 2020

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] ;
NameCityYear
AnkitDelhi2019
BabitaNoida2017
ChetanNoida2018
DeepakDelhi2018
IshaDelhi2019
KhushiNoida2019
MeghaNoida2017
ParulNoida2017

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 :

NameYearLast_City
Deepak2018Delhi
Isha2019Delhi
Ankit2019Delhi
Babita2017Noida
Chetan2018Noida
Khushi2019Noida
Megha2017Noida
Parul2017Noida

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 :

NameYearLast_City
Babita2017Noida
Megha2017Noida
Parul2017Noida
Deepak2018Delhi
Chetan2018Noida
Isha2019Delhi
Ankit2019Delhi
Khushi2019Noida

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :