mssqltips logo

SQL Server 2012 Functions - First_Value and Last_Value

By:   |   Updated: 2012-03-12   |   Comments (20)   |   Related: 1 | 2 | 3 | 4 | More > Functions - System

Problem

SQL Server 2012 introduces new analytical functions FIRST_VALUE and LAST_VALUE.  These new functions allow you to get the same value for the first row and the last row for all records in a result set.  In this tip we will explore these functions and how to use them.

Solution

SQL Server 2012 introduces two new analytical functions FIRST_VALUE and LAST_VALUE.

  • FIRST_VALUE returns the first value in an ordered set of values and
  • LAST_VALUE returns the last value in an ordered set of values

The syntax for these functions are:

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

Let me explain this using an example.

The following script creates a test table and some test data.

CREATE DATABASE [TestDB]
--Create testable to hold some data
CREATE TABLE [dbo].[Test_table](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Department] [nchar](10) NOT NULL,
 [Code] [int] NOT NULL,
 CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

So our table data will look like:

sql result set

Now the query for FIRST_VALUE and LAST_VALUE will be:

SELECT id,department,code,
FIRST_VALUE(code) OVER (ORDER BY code) FstValue,
LAST_VALUE(code) OVER (ORDER BY code) LstValue
FROM test_table

Here are the results:

sql first_value and last_value functions

In the above example FIRST_VALUE is the same and equal to the value in the first row (i.e. 15) for the entire result set. While the LAST_VALUE changes for each record and is equal to the last value that was pulled (i.e. current value in the result set). 

Get Same Last Value for All Records

If we want the Last Value to remain the same for all rows in the result set we need to use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING with the LAST_VALUE function as shown below.

SELECT id,department,code,
FIRST_VALUE(code) OVER (ORDER BY code) FstValue,
LAST_VALUE(code) OVER (ORDER BY code
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
FROM test_table

Here are the results:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Getting Different First And Last Values By Groups

Now let's use these functions with the PARTITION BY clause. The partition by clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group, as we saw in last example. To test this we will insert some more test data where values for department will be duplicated.

insert into Test_table values( 'A',51)
insert into Test_table values( 'A',111)
insert into Test_table values( 'A',169)
insert into Test_table values( 'A',514)
insert into Test_table values( 'B',5331)
insert into Test_table values( 'B',12211)
insert into Test_table values( 'B',101)
insert into Test_table values( 'B',135)

Now the query will look like this:

SELECT id,department,code,
FIRST_VALUE(code)  OVER (PARTITION BY department ORDER BY code) FstValue,
LAST_VALUE(code) OVER (PARTITION BY department ORDER BY code) LstValue
FROM test_table

So the output would be:

sql first_value last_value partition by

Now the result set is divided into partitions based on the department, so the FIRST_VALUE is different but the same for each partition, while the LAST_VALUE changes for the last row in that partition.

Next Steps


Last Updated: 2012-03-12


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, February 25, 2015 - 10:28:28 AM - Thomas Franz Back To Top

And one again a (simplified) real world example:

You have the following table:

create table test     (id int,
                       group_name  varchar(10),
                       value       varchar(100),
                       is_default  bit
                      )
;
insert into test
values (01, 'grp1', 'value 01', 0),
       (02, 'grp2', 'value 02', 0),
       (03, 'grp3', 'value 03', 1),
       (04, 'grp1', 'value 04', 0),
       (05, 'grp2', 'value 05', 1),
       (06, 'grp3', 'value 06', 0),
       (07, 'grp1', 'value 07', 0),
       (08, 'grp2', 'value 08', 0),
       (09, 'grp3', 'value 09', 0),
       (10, 'grp1', 'value 10', 1)

Now you want to query the group's default per id without joining the table to a subset (with default = 1) of itself:

select id,
       value,
       group_name,
       FIRST_VALUE(value) over (partition by group_name order by is_default desc, id) default_value_for_this_group
  from test
 order by group_name, id


Remark: if there are more than one default value per group (because you have no filtered unique index on group_name and is_default where is_default = 1) it would return the one with the lowest id (because of the order clause)

 


Friday, February 20, 2015 - 3:25:43 AM - Thomas Franz Back To Top

Another real world example:

You have an error log table which logs all errors with device_id, part_id, error_time and reason. Now you want to query the very last part / time and error reason for each device. Min()/Max() would not help you in this case because you could get the max(error_time) but Max(part_id) or Max(reason) would not give you the part_id / reason from the last event. Without FIRST_VALUE / LAST_VALUE you would have to join the table to itself by using the max(error_time) and device_id as join parameters, but by using this functions the query would be much easier:


select distinct device_id,
       FIRST_VALUE(part_id) over (partition by device_id order by error_time desc) LastErrorPart ,
       FIRST_VALUE(error_time) over (partition by device_id order by error_time desc) LastErrorTime,
       FIRST_VALUE(reason) over (partition by device_id order by error_time desc) LastErrorReason
  from error_log
order by device_id
;



Friday, October 10, 2014 - 3:52:02 PM - Walter Meester Back To Top

Why not have a NthValue( function where you can specify whether you want the 1st, 2nd, 3rd or last value? This seems like half baked useful.


Tuesday, June 11, 2013 - 9:35:48 AM - Dave Back To Top

To answer "when" to use this...

I find that these functions and the OVER clause in general very useful in debuging my data.  Previously we used were either limited to GROUP BYs where we could not get all the details of the rows in question or we had to do nested queries that were hard to read and potentially performed poorly.  Now it's easier to find problem rows and see all details of the rows.  These functions would be even better if you could used them in a WHERE clause.  Say my business rules are that I should only have one row per department. I'd love to write a query like this.

SELECT COUNT(*) OVER (PARTITION BY department) AS RowCnt, * FROM test_table

WHERE COUNT(*) OVER (PARTITION BY department) > 1

It would give me the details of the rows so I can debug why it happened.  I can do it without and OVER clause using GROUP BY and HAVING but that only gets me the department.  I have to use nested queries to get all the details.

Unfortunatly you can't use the OVER clause in the WHERE clause.  So to do this you have to use nested queries anyway.  I still prefer to use the OVER clause because the nesting is simplier.

SELECT * FROM (

SELECT COUNT(*) OVER (PARTITION BY department) AS RowCnt, * FROM test_table) AS subquery

WHERE RowCnt > 1


Tuesday, March 27, 2012 - 11:48:54 AM - Mannie Back To Top

OK, I surrender..."No habla ingles" spoken here.


Tuesday, March 27, 2012 - 10:48:50 AM - rajendra gupta Back To Top

Mannie TIP has been reviewed and updated. Please check if it looks fine now.


Tuesday, March 27, 2012 - 7:38:22 AM - Mannie Back To Top

Not trying to belabor the point Greg, but, no, the "time travel" experience is still there--it's actually worse than before.  If you read the article--code, comments, and results--from the top down, you will see what I mean. 

First, create table, insert 15 rows, plain select of 15 rows; the results look normal. Next, the text says select using UNBOUNDED, but the "results" code is not UNBOUNDED but OVER(PARTITION BY...) with results containing values (department='B', code=12211); these values were NOT part of the 15 rows initially inserted. Next is text stating the next example will use PARTITION BY clause, but first insert eight more rows (containing the telltale department='B' and code=12211). However, the "results" code is now the UNBOUNDED code which again (but now legitimately) contains the values (department='B', code=12211).

Here endeth the lesson.

To actually correct the tip is not simply a matter of rearranging images, but resequencing the text and examples in the article.  Showing a screenshot image which doesn't match the text can understood (stuff happens), but showing query results containing data which has not yet been inserted cannot be understood.  You start scratching your head and say, "What the heck is this guy talking about?"  Not the reaction you want...


Monday, March 26, 2012 - 5:51:40 PM - Greg Robidoux Back To Top

Partha - I think Allan is saying that you had to get the first value and then get the last value and then use these two values in your overall recordset. So basically doing 3 queries.  It was definetly much more work without this new feature.


Monday, March 26, 2012 - 5:31:22 PM - partha Back To Top

@Allan Hansen 

What you are talking about is min and max functions which have been there for quite some time. This does not behave like that.



Monday, March 26, 2012 - 5:01:09 PM - Greg Robidoux Back To Top

My mistake, I updated the wrong images. I think the images make sense now.


Monday, March 26, 2012 - 11:40:16 AM - Mannie Back To Top

Greg - I must be too quick in looking at the updated tip...it looks the same to me.  Create a table, insert 15 rows, select 15 rows--so far, so good.  Select again, this time using UNBOUNDED clauses, and results show 23 rows???  THEN, insert 8 more rows (total rows are 23 only after the second insert.)  My hint would be to move the second INSERT before the select with UNBOUNDED.


Monday, March 26, 2012 - 10:07:28 AM - Greg Robidoux Back To Top

Mannie - thanks for pointing out the differences for the examples.  The tip has been updated to use the same data set for all examples.


Friday, March 23, 2012 - 12:04:52 PM - Mannie Back To Top

Thanks for the time travel tips...for a while I couldn't figure out how you managed to retrieve 23 rows from a 15-row table before you inserted another 8 rows.  Then I realized "UNBOUNDED FOLLOWING" allows time travel into the future.  Exciting stuff!  Albert Einstein was impressed...next year.


Monday, March 19, 2012 - 10:35:48 AM - bhanu.yenugonda Back To Top

Thanks alot rajendra for the nice explanation.


Tuesday, March 13, 2012 - 3:52:43 AM - jegadhesan Back To Top

Jegadhesan -- Really Use Full and good


Tuesday, March 13, 2012 - 2:57:16 AM - Allan Hansen Back To Top

The real world application is that you occasionally need to know the first and last value in a recordset - for example - what's the minimum or maximum price of a product. What's the minimum or maximum stock amount. What's the maximum and minimum order values. Stuff like that.


In the 'old days' (so to speak) you'd do some order by desc and acs and do a TOP 1 twice. Now you can get them in one query.


Tuesday, March 13, 2012 - 1:45:52 AM - Koushal Jha Back To Top

where we can use these functions ?


Monday, March 12, 2012 - 12:50:31 PM - Lakshmi Back To Top

Hi,

Excellent Sql Tips.

 

Thanks,

Lakshmi


Monday, March 12, 2012 - 10:28:57 AM - partha Back To Top

What are real world scenarios where we would use these functions?


Monday, March 12, 2012 - 7:44:11 AM - Mohamed Irshad Back To Top

Nice and cool explanation of the new functions available. Excellent. Well Done.

Mohamed Irshad



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools