last_value function in sql server 2008
sql server last_value function returns incorrect data
sql server last_value function example
sql server last_value function with partition example

LAST_VALUE function in SQL Server

In this video we will discuss LAST_VALUE function in SQL Server.

LAST_VALUE function
Introduced in SQL Server 2012
Retrieves the last value from the specified column
ORDER BY clause is required
PARTITION BY clause is optional
ROWS or RANGE clause is optional, but for it to work correctly you may have to explicitly specify a value

Syntax : LAST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, …)

LAST_VALUE function not working as expected : In the following example, LAST_VALUE function does not return the name of the highest paid employee. This is because we have not specified an explicit value for ROWS or RANGE clause. As a result it is using it’s default value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (ORDER BY Salary) AS LastValue
FROM Employees

LAST_VALUE function working as expected : In the following example, LAST_VALUE function returns the name of the highest paid employee as expected. Notice we have set an explicit value for ROWS or RANGE clause to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

This tells the LAST_VALUE function that it’s window starts at the first row and ends at the last row in the result set.

SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue
FROM Employees

LAST_VALUE function example with partitions : In the following example, LAST_VALUE function returns the name of the highest paid employee from the respective partition.

SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue
FROM Employees

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

Full SQL Server Course

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Nguồn: https://dorifaucet.com/

Xem thêm bài viết khác: https://dorifaucet.com/cong-nghe/

15 Comments

  • Aiman azeem
    Posted July 9, 2020 9:27 am

    Thank you.. This video is really helpful my issue is solved atlast. But I add top 1 also before last_value because I was using it in subquery and it was returning many values.. Again Thanks for this video..!

  • Dhanashree Deshmukh-Dhamal
    Posted July 9, 2020 9:27 am

    Thank you is not enough for your selfless act

  • rohit sethi
    Posted July 9, 2020 9:27 am

    NEXT VALUE FOR

  • Jhansi Bode
    Posted July 9, 2020 9:27 am

    how to replace with previous record value with empty record ,can you pls help me with the answer

  • Thato Kamogelo Motaung
    Posted July 9, 2020 9:27 am

    what if i wanna get the current value and all the previous values that were stored for a specific ID?

  • Shanmugavel Sugumar
    Posted July 9, 2020 9:27 am

    I think last name is same as using first value() over(order by salary desc)

  • Sumathi
    Posted July 9, 2020 9:27 am

    sir my question is write a query to get the who registered to last day of previous day?

  • Sandeep Sharma
    Posted July 9, 2020 9:27 am

    sir can you tell us how to write this query in netbeans when i try its not work

  • Krzysztof S
    Posted July 9, 2020 9:27 am

    Venkat rules as always! Thank you Venkat for educating community and have a great day and life ! We are very thankful !

  • shiva prasad
    Posted July 9, 2020 9:27 am

    Thanks for sharing the new features. SQL paging 2012 OFFSET using order by fetch and also more sorting. i want detailed description.
    Thanku

  • raqibul1000000 Alam
    Posted July 9, 2020 9:27 am

    I enjoy your tutorial just like a Box office hit Hollywood movie.Thanks a billion.

  • Girijesh Kumar
    Posted July 9, 2020 9:27 am

    Great Sir !!! again you have recorded a very helpful video for us thank you so much sir for doing this. can you please upload a video for Inversion of control (IOC) and Dependency Injection (DI) for MVC this is very common interview question we are facing. it would be great help to us. I watched almost your all videos and I found your explanation is very very great and clear no other one can be "Kudvenkat" in this world…great salute sir.

  • Muhammad Rehbar Sheikh
    Posted July 9, 2020 9:27 am

    Thanks sir!!

  • TAN YEW MENG
    Posted July 9, 2020 9:27 am

    I would like to express my sincere gratitude and appreciation for your dedication, selfless, passionate, and hard work. We cannot thank you enough who worked tirelessly behind the scenes contributing to the .NET community through your amazing and superb tutorial videos.

  • Paulo Ts
    Posted July 9, 2020 9:27 am

    Everyday around the same time I come to check if there is another new SQL video, and everyday I get happy about this 🙂

Leave a comment