SQL SERVER – Introduction to FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 introduces new analytical functions FIRST_VALUE() and LAST_VALUE(). This function returns first and last value from the list. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes.

Now let’s have fun following query:

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

The above query will give us the following result:

SQL SERVER - Introduction to FIRST _VALUE and LAST_VALUE  - Analytic Functions Introduced in SQL Server 2012 firstvalue1

Solarwinds

What’s the most interesting thing here is that as we go from row 1 to row 10, the value of the FIRST_VALUE() remains the same but the value of the LAST_VALUE is increasing. The reason behind this is that as we progress in every line – considering that line and all the other lines before it, the last value will be of the row where we are currently looking at. To fully understand this statement, see the following figure:

SQL SERVER - Introduction to FIRST _VALUE and LAST_VALUE  - Analytic Functions Introduced in SQL Server 2012 firstvalue2

This may be useful in some cases; but not always. However, when we use the same thing with PARTITION BY, the same query starts showing the result which can be easily used in analytical algorithms and needs.

Let us have fun through the following query:

Let us fun following query.
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

The above query will give us the following result:

SQL SERVER - Introduction to FIRST _VALUE and LAST_VALUE  - Analytic Functions Introduced in SQL Server 2012 firstvalue3

Let us understand how PARTITION BY windows the resultset.

I have used PARTITION BY SalesOrderID in my query. This will create small windows of the resultset from the original resultset and will follow the logic or FIRST_VALUE and LAST_VALUE in this resultset.

SQL SERVER - Introduction to FIRST _VALUE and LAST_VALUE  - Analytic Functions Introduced in SQL Server 2012 firstvalue4

Well, this is just an introduction to these functions. In the future blog posts we will go deeper to discuss the usage of these two functions. By the way, these functions can be applied over VARCHAR fields as well and are not limited to the numeric field only.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQLAuthority News – Updates on Contests, Books and SQL Server
Next Post
SQL SERVER – OVER clause with FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012 – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Related Posts

30 Comments. Leave new

  • Hi Pinal,

    You have been a useful resource to me over the years. For the Last_Value, I believe you have not told the entire story.

    The example you have leads to believe that Last_Value is useless because it does not return the last SalesOrderID value. The problem is you are using the default framing which would be equivalent to last_value(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
    ORDER BY SalesOrderDetailID ROWS Between Unbounded Preceding AND Current Row). It is the Current Row end point which is the problem. A better application would be to override the default and use the following: last_value(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
    ORDER BY SalesOrderDetailID ROWS Unbounded Preceding AND Unbounded Following) which would get the expected behavior.

    Fred

    Reply
  • Patricia Xavier
    February 6, 2016 1:11 am

    thank you very much!

    Reply
  • You’re a great educator. I wish you well in everything you do.

    Reply
  • An alternative to using ” ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING” to get the last value in a group is to use first_value but order descending

    Reply
  • Hello,

    Thank you so much for explaining these features.
    However I wonder if there is a solution to avoid / ignore NULL values when calculating the LAST_VALUE.

    Example :

    YYYYMM —– ProductID —– Value
    201701 —– P01 —– 10
    201702 —– P01 —– 20
    201703 —– P01 —– 30
    201704 —– P01 —– 40
    201705 —– P01 —– 50
    201706 —– P01 —– 60
    201707 —– P01 —– 70
    201708 —– P01 —– 80
    201709 —– P01 —– 90
    201710 —– P01 —– NULL
    201711 —– P01 —– NULL
    201712 —– P01 —– NULL

    I want to use LAST_VALUE function as below :
    LAST_VALUE(Value) OVER (Partition By ProductID ORDER BY YYYYMM)

    to get :

    YYYYMM —– ProductID —– Value —– NewValue
    201701 —– P01 —– 10 —– 10
    201702 —– P01 —– 20 —– 20
    201703 —– P01 —– 30 —– 20
    201704 —– P01 —– 40 —– 20
    201705 —– P01 —– 50 —– 20
    201706 —– P01 —– 60 —– 20
    201707 —– P01 —– 70 —– 20
    201708 —– P01 —– 80 —– 20
    201709 —– P01 —– 90 —– 20
    201710 —– P01 —– NULL —– 90
    201711 —– P01 —– NULL —– 90
    201712 —– P01 —– NULL —– 90

    But it doesn’t work.

    Any idea please ?


    Best regards,
    GK

    Reply
    • More precision : (reply to myself as GK as I can’t edit my previous comment)

      It doesn’t care if 90 is the highest value, I mean that even if it is 0, it must be considered as the latest not null value and it must replace the values of 201710, 201711 and 201712.

      Thanks in advance.
      GK

      Reply
  • Naomi Nosonovsky
    February 19, 2018 4:45 am

    Thanks! That was helpful

    Reply
  • where can I find the real time usage of it

    Reply

Leave a Reply

Menu