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] ;
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 |
Share your thoughts in the comments
Please Login to comment...