SQL Server 2012 – First Value Last Value

Continuing my series on SQL Server 2012, today we will explore two new functions in SQL Server 2012. SQL Server 2012 has introduced two new functions first_value() and last_value(). In previous SQL Server versions, if you want to return the first value or last value along with the result set, you need to use self join. But from version 2012, you can simply use first_value() and last_value() function

Consider the following set of data

create table #t(id int, names varchar(100), join_date datetime)

insert into #t (id,names,join_date)
select 1,'test1','20121219' union all
select 1,'test1','20121212' union all
select 2,'test2','20110101' union all
select 3,'test3','20120918' union all
select 4,'test4','20081112' union all
select 5,'test5','20071226' union all
select 6,'test6','20000101' union all
select 7,'test7','20040703' union all
select 8,'test8','20090829' union all
select 9,'test9','20120831' union all
select 10,'test10','20030914'

The following query will show the first_value and last_value along with the result set.

select
    id,
    names,
    first_value(join_date) over (order by id) as first_join_date,
    last_value(join_date) over (order by id) as last_join_date
from
    #t

First_value() returns the first available value in the ordered set while last_value() returns the last value available. It should be noted that these values are calculated for each row. So the value returned by last_value() changes from row to row. first_value is same for the resultset, while the last_value is dynamically changed for each row

OUTPUT

If you want to reset these values for each group of id, you can use partition clause as you use in windows functions

select
    id,
    names,
    first_value(join_date) over (partition by id order by id) as first_join_date,
    last_value(join_date) over (partition by id order by id) as last_join_date
from
    #t

The above returns the first_value and last_value available in the result set and reset those value for each value of id

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan