Appear in Online Interview to test your skills and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Make friends on DotNetFunda.com and expand your network!
Winners

Win Prizes

Announcements
Like us on Facebook
Sponsor
Top Articles Author
Thu, 24-Apr-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
34800
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

Let's learn Last_Value Function in Sql Server 2012

Niladri.Biswas
Posted by under Sql Server category on for Beginner level | Points: 250 | Views : 1335 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.


In this article we will learn Last_Value function of Sql Server 2012 (Denali).

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Last_Value Function.

Purpose:

It returns the last value from the order set of values.

Syntax:

Last_Value(expression) over( [ Partition_By_clause] order by clause [rows_range_clause])

Where,

Expression => A table column or built-in function but not analytical functions.

Rows_range_clause => It helps to further limit the effect of analytical function.

As said, the Last _Value function returns the last value from the order set of values. If the last value is null, then the function returns null.

Let us see this function into action.

Case study

Let us first create the environment

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
    DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
        [MatchID] [int] IDENTITY(1,1) NOT NULL,
        [MatchGroup] [varchar](8) NULL,
        [MatchBetween] [varchar](50) NULL,
        [ScheduleDate] [date] NULL
) ON [PRIMARY]
GO

--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')

-- Project the records
Select * From MatchTable

/* Result
MatchID MatchGroup      MatchBetween            ScheduleDate
1       Group-A         India VS Australia      2011-08-14
2       Group-A         India VS Pakistan       2011-08-15
3       Group-A         India VS Newzealand     2011-08-16
4       Group-A         Australia VS Pakistan   2011-08-17
5       Group-A         Australia VS Newzealand 2011-08-18
6       Group-A         Newzealand VS Pakistan  2011-08-19
7       Group-B         USA VS WestIndies       2011-08-20
8       Group-B         USA VS Ireland          2011-08-21
9       Group-B         USA VS Bangaladesh      2011-08-22
10      Group-B         WestIndies VS Ireland   2011-08-23
11      Group-B         WestIndies VS Bangaladesh2011-08-24
12      Group-B         Ireland VS Bangaladesh  2011-08-25
*/

Example 1: Simple Last_Value demo

Suppose we need to find out the last match played in every group. We can achieve it as under

Select 	  
	 MatchBetween  
	 ,MatchGroup
	 ,Last_Value(MatchBetween) Over(Partition By MatchGroup Order By MatchGroup) As [Last Match In Group]
From MatchTable

/* Result

MatchBetween		MatchGroup	Last Match In Group
------------		---------	---------------------
India VS Australia	Group-A		Newzealand VS Pakistan
India VS Pakistan	Group-A		Newzealand VS Pakistan
India VS Newzealand	Group-A		Newzealand VS Pakistan
Australia VS Pakistan	Group-A		Newzealand VS Pakistan
Australia VS Newzealand	Group-A		Newzealand VS Pakistan
Newzealand VS Pakistan	Group-A		Newzealand VS Pakistan
USA VS WestIndies	Group-B		Ireland VS Bangaladesh
USA VS Ireland		Group-B		Ireland VS Bangaladesh
USA VS Bangaladesh	Group-B		Ireland VS Bangaladesh
WestIndies VS Ireland	Group-B		Ireland VS Bangaladesh
WestIndies VS BangaladeshGroup-B	Ireland VS Bangaladesh
Ireland VS Bangaladesh	Group-B		Ireland VS Bangaladesh

*/

Example 2: Last_Value with new windowing clause

Select 	 
	   MatchBetween  
	   ,ScheduleDate	   
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPrec
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPrec
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Current Row) As Rng_UPrec_Curr
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Current Row) As Row_UPrec_Curr
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Unbounded Following) As Rng_UPrec_UFoll
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Unbounded Following) As Row_UPrec_UFoll
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbounded Following) As Rng_Curr_UFoll
	   ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounded Following) As Row_Curr_UFoll	   
From MatchTable

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Last_Value function and it's various usage.Hope the article will be useful.

Thanks for reading

Page copy protected against web site content infringement by Copyscape
Found interesting? Add this to:




About the Author

Niladri.Biswas

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com

Technical Lead at HCL Technologies Ltd.
Login to vote for this post.

Comments or Responses


Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.

Comment using Facebook