Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

First_Value function in Denali (SQL Server 2012) CTP 3

Aug 31 2011 2:35AM by Niladri Biswas   

First_Value Function

Purpose: It returns the first value from the order set of values.

Syntax: First_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 First_Value function returns the first value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.

Before going to look into some examples of First_Value we will first create the test environment

Run the below script for doing so

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 First_Value demo for getting the first and the last match played

Suppose we need to find out the first and the last match played. We can achieve it as under


Select 	  
	 MatchBetween  
	 ,ScheduleDate
	 ,First_Value(MatchBetween) Over(Order By ScheduleDate  Desc) As [Last Match]
	 ,First_Value(MatchBetween) Over(Order By ScheduleDate ) As [First Match]
From MatchTable

/* Result

MatchBetween		ScheduleDate	Last Match		First Match
India VS Australia	2011-08-14	Ireland VS Bangaladesh	India VS Australia
India VS Pakistan	2011-08-15	Ireland VS Bangaladesh	India VS Australia
India VS Newzealand	2011-08-16	Ireland VS Bangaladesh	India VS Australia
Australia VS Pakistan	2011-08-17	Ireland VS Bangaladesh	India VS Australia
Australia VS Newzealand	2011-08-18	Ireland VS Bangaladesh	India VS Australia
Newzealand VS Pakistan	2011-08-19	Ireland VS Bangaladesh	India VS Australia
USA VS WestIndies	2011-08-20	Ireland VS Bangaladesh	India VS Australia
USA VS Ireland		2011-08-21	Ireland VS Bangaladesh	India VS Australia
USA VS Bangaladesh	2011-08-22	Ireland VS Bangaladesh	India VS Australia
WestIndies VS Ireland	2011-08-23	Ireland VS Bangaladesh	India VS Australia
WestIndies VS Bangaladesh2011-08-24	Ireland VS Bangaladesh	India VS Australia
Ireland VS Bangaladesh	2011-08-25	Ireland VS Bangaladesh	India VS Australia

*/

Well, by looking into the result set for this anyone can argue that, it can be achieved by

a)Row_Number approach as

;With Cte AS(
Select 
		RowID = ROW_NUMBER() Over(Order by (select 1))
		,MatchBetween  
		,ScheduleDate
From MatchTable )

Select 
		MatchBetween  
		,ScheduleDate
		, [Last Match] = (Select MatchBetween  from Cte where RowID = 12)
		, [First Match] = (Select MatchBetween  from Cte where RowID = 1)
from Cte

N.B.~ I have deliberately used Row_Number() function instead of the [MatchID] column just to get the work done using Row_Number() way.

b)Max/Min approach as

Select 
	MatchBetween  
	,ScheduleDate
	, [Last Match] = (Select MatchBetween  from MatchTable where ScheduleDate = (Select MAX(ScheduleDate) from MatchTable))
	, [First Match] = (Select MatchBetween from MatchTable where ScheduleDate = (Select MIN(ScheduleDate) from MatchTable))
from MatchTable

And this argument will be valid. But wait, is it the only purpose of First_Value() function? Let’s see some more example.

Example 2: Simple First_Value demo with Partition By clause

Select 	 
	   MatchBetween  
	   ,ScheduleDate	
	   ,MatchGroup   
	   ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate Desc) As LastMatch
	   ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate) As FirstMatch	   
From MatchTable

/* Result

MatchBetween		ScheduleDate	MatchGroup	LastMatch		FirstMatch
India VS Australia	2011-08-14	Group-A		Newzealand VS Pakistan	India VS Australia
India VS Pakistan	2011-08-15	Group-A		Newzealand VS Pakistan	India VS Australia
India VS Newzealand	2011-08-16	Group-A		Newzealand VS Pakistan	India VS Australia
Australia VS Pakistan	2011-08-17	Group-A		Newzealand VS Pakistan	India VS Australia
Australia VS Newzealand	2011-08-18	Group-A		Newzealand VS Pakistan	India VS Australia
Newzealand VS Pakistan	2011-08-19	Group-A		Newzealand VS Pakistan	India VS Australia
USA VS WestIndies	2011-08-20	Group-B		Ireland VS Bangaladesh	USA VS WestIndies
USA VS Ireland		2011-08-21	Group-B		Ireland VS Bangaladesh	USA VS WestIndies
USA VS Bangaladesh	2011-08-22	Group-B		Ireland VS Bangaladesh	USA VS WestIndies
WestIndies VS Ireland	2011-08-23	Group-B		Ireland VS Bangaladesh	USA VS WestIndies
WestIndies VS Bangaladesh2011-08-24	Group-B		Ireland VS Bangaladesh	USA VS WestIndies
Ireland VS Bangaladesh	2011-08-25	Group-B		Ireland VS Bangaladesh	USA VS WestIndies

*/

As can be make out that, in this case the function is working based on the partitions made.

Example 3: First_Value with the enhanced over clause

We have already covered Over clause enhancement.It's time to now see it's effect with the First_Value

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

/* Result

MatchBetween	ScheduleDate	Rng_UPrec	Row_UPrec	Rng_Curr	Row_Curr	Rng_UPrec_Curr	Row_UPrec_Curr	Rng_UPrec_UFoll	Row_UPrec_UFoll	Rng_Curr_UFoll	Row_Curr_UFoll
Ireland VS Bangaladesh	2011-08-25	India VS Australia	India VS Australia	Ireland VS Bangaladesh	Ireland VS Bangaladesh	India VS Australia	India VS Australia	India VS Australia	India VS Australia	Ireland VS Bangaladesh	Ireland VS Bangaladesh
WestIndies VS Bangaladesh	2011-08-24	India VS Australia	India VS Australia	WestIndies VS Bangaladesh	WestIndies VS Bangaladesh	India VS Australia	India VS Australia	India VS Australia	India VS Australia	WestIndies VS Bangaladesh	WestIndies VS Bangaladesh
WestIndies VS Ireland	2011-08-23	India VS Australia	India VS Australia	WestIndies VS Ireland	WestIndies VS Ireland	India VS Australia	India VS Australia	India VS Australia	India VS Australia	WestIndies VS Ireland	WestIndies VS Ireland
USA VS Bangaladesh	2011-08-22	India VS Australia	India VS Australia	USA VS Bangaladesh	USA VS Bangaladesh	India VS Australia	India VS Australia	India VS Australia	India VS Australia	USA VS Bangaladesh	USA VS Bangaladesh
USA VS Ireland	2011-08-21	India VS Australia	India VS Australia	USA VS Ireland	USA VS Ireland	India VS Australia	India VS Australia	India VS Australia	India VS Australia	USA VS Ireland	USA VS Ireland
USA VS WestIndies	2011-08-20	India VS Australia	India VS Australia	USA VS WestIndies	USA VS WestIndies	India VS Australia	India VS Australia	India VS Australia	India VS Australia	USA VS WestIndies	USA VS WestIndies
Newzealand VS Pakistan	2011-08-19	India VS Australia	India VS Australia	Newzealand VS Pakistan	Newzealand VS Pakistan	India VS Australia	India VS Australia	India VS Australia	India VS Australia	Newzealand VS Pakistan	Newzealand VS Pakistan
Australia VS Newzealand	2011-08-18	India VS Australia	India VS Australia	Australia VS Newzealand	Australia VS Newzealand	India VS Australia	India VS Australia	India VS Australia	India VS Australia	Australia VS Newzealand	Australia VS Newzealand
Australia VS Pakistan	2011-08-17	India VS Australia	India VS Australia	Australia VS Pakistan	Australia VS Pakistan	India VS Australia	India VS Australia	India VS Australia	India VS Australia	Australia VS Pakistan	Australia VS Pakistan
India VS Newzealand	2011-08-16	India VS Australia	India VS Australia	India VS Newzealand	India VS Newzealand	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Newzealand	India VS Newzealand
India VS Pakistan	2011-08-15	India VS Australia	India VS Australia	India VS Pakistan	India VS Pakistan	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Pakistan	India VS Pakistan
India VS Australia	2011-08-14	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia	India VS Australia
*/

Hope this helps

Tags: sql 11, #SQLServer, SQL Server, TSQL, #TSQL, Denali CTP3, SQL Server 2012,


Niladri Biswas
7 · 21% · 6710
2 Readers Liked this
Sandeep Prajapati Liked this on 9/9/2011 6:26:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 8/1/2012 2:13:00 AM
Profile · Blog
2
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"First_Value function in Denali (SQL Server 2012) CTP 3" rated 5 out of 5 by 2 readers
First_Value function in Denali (SQL Server 2012) CTP 3 , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]