FIRST_VALUE – New analytical function in SQL Server 2012


In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

Advertisements

Tagged: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: