HomeОбразованиеRelated VideosMore From: kudvenkat

LAST VALUE function in SQL Server

162 ratings | 37799 views
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 http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server_9.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html Full SQL Server Course https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4 All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Html code for embedding videos on your blog
Text Comments (11)
Thato Kamogelo Motaung (2 months ago)
what if i wanna get the current value and all the previous values that were stored for a specific ID?
Shanmugavel Sugumar (5 months ago)
I think last name is same as using first value() over(order by salary desc)
Sumathi (1 year ago)
sir my question is write a query to get the who registered to last day of previous day?
Sandeep Sharma (2 years ago)
sir can you tell us how to write this query in netbeans when i try its not work
Krzysztof S (2 years ago)
Venkat rules as always! Thank you Venkat for educating community and have a great day and life ! We are very thankful !
shiva prasad (3 years ago)
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 (3 years ago)
I enjoy your tutorial just like a Box office hit Hollywood movie.Thanks a billion.
Girijesh Kumar (3 years ago)
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 (3 years ago)
Thanks sir!!
Tan YEW MENG (3 years ago)
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 (3 years ago)
Everyday around the same time I come to check if there is another new SQL video, and everyday I get happy about this :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.