GeeksforGeeks App
Open App
Browser
Continue

# 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] ;```
NameCityYear
AnkitDelhi2019
BabitaNoida2017
ChetanNoida2018
DeepakDelhi2018
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
My Personal Notes arrow_drop_up