SQL SERVER – OVER clause with FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012 – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Yesterday I had discussed two analytical functions FIRST_VALUE and LAST_VALUE. After reading the blog post I received very interesting question.

“Don’t you think there is bug in your first example where FIRST_VALUE is remain same but the LAST_VALUE is changing every line. I think the LAST_VALUE should be the highest value in the windows or set of result.”

I find this question very interesting because this is very commonly made mistake. No there is no bug in the code. I think what we need is a bit more explanation. Let me attempt that first. Before you do that I suggest you read yesterday’s blog post as this question is related to that blog post.

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:

As per the reader’s question the value of the LAST_VALUE function should be always 114 and not increasing as the rows are increased. Let me re-write the above code once again with bit extra T-SQL Syntax. Please pay special attention to the ROW clause which I have added in the above syntax.

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

Now once again check the result of the above query.

The result of both the query is same because in OVER clause the default ROWS selection is always UNBOUNDED PRECEDING AND CURRENT ROW.

If you want the maximum value of the windows with OVER clause you need to change the syntax to UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for ROW clause. Now run following query and pay special attention to ROW clause again.

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

Here is the resultset of the above query which is what questioner was asking. So in simple word, there is no bug but there is additional syntax needed to add to get your desired answer.

The same logic also applies to PARTITION BY clause when used. Here is quick example of how we can further partition the query by SalesOrderDetailID with this new functions.

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

Above query will give us windowed resultset on SalesOrderDetailsID as well give us FIRST and LAST value for the windowed resultset.

There are lots to discuss for this two functions and we have just explored tip of the iceberg. In future post I will discover it further deep.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – OVER clause with FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012 – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  1. Sir,
    I have one question :-
    Referring to the diagram 3 of the article.

    Do the LAST_VALUE and FIRST_VALUE functions take into account the max and min
    values in the given set or they are just based out of the last and first
    values depending on the sorting.

    As in say if the order by was diff and on row 4 we had the SalesOrderDetailID = 80
    and on row 5 we had it = 79. then what would be the LstValue of these rows
    respectively.

    Will it be (as given in the diagram)
    Row 4 : 80
    Row 5 : 80

    or will it be
    Row 4 : 80
    Row 5 : 79

    Thank you for this and all the articles on this site. they are really very very helpful.

    –Nilesh

    Like

  2. Hi pinal sir,
    Thanks for this explanation.

    We can say FIRST_VALUE and LAST_VALUE is advanced version of MIN and MAX functions of SQL Server with OVER function.
    Till now if we want any column with with MIN or MAX we have to used group by clause.
    And also with group by we have limited rows of data.

    So its because of OVER function we got First_Value and Last_Value.

    Regard$
    Chirag Satasiya

    Like

  3. Pingback: SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY « Journey to SQLAuthority

  4. In the article this query

    SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
    FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
    ORDER BY SalesOrderDetailID
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
    LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
    ORDER BY SalesOrderDetailID
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
    FROM Sales.SalesOrderDetail s
    WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
    ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
    GO

    returns:

    SalesOrderID SalesOrderDetailID OrderQty FstValue LstValue
    43663 52 1 52 114
    43667 77 3 52 114
    43667 78 1 52 114
    43667 79 1 52 114
    43667 80 1 52 114
    43669 110 1 52 114
    43670 111 1 52 114
    43670 112 2 52 114
    43670 113 2 52 114
    43670 114 1 52 114

    this is wrong.

    This query will return the result above

    SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
    FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY 1
    ORDER BY SalesOrderDetailID
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
    LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY 1
    ORDER BY SalesOrderDetailID
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
    FROM Sales.SalesOrderDetail s
    WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
    ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
    GO

    Like

  5. Pingback: SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority « Journey to SQLAuthority

  6. I am having similar query problem but i can’t slove using Fist and last value as SQL 2008 doesn’t support it….Can any one help me please………

    Below is the table:

    Col1 Col2 Col3 Col4
    ———– ———– ———– —————–
    1 1 120 0.1243 ….1st Row
    1 1 130 0.1211
    1 1 140 0.1117
    1 1 150 0.1298
    1 1 160 0.1243
    1 1 170 0.1180
    1 1 180 0.1167
    1 1 190 0.1191
    1 1 200 0.1231
    1 1 210 0.1185 ….10th Row
    1 1 220 0.1287 ….11th Row
    1 1 230 0.1058
    1 1 240 0.1104
    1 1 250 0.1270
    1 1 260 0.1127
    1 2 120 0.1191
    1 2 130 0.1169
    1 2 140 0.1101
    1 2 150 0.1272
    1 2 160 0.1125 …..20th Row

    Output I am looking for is: Average(Col4) for every 10 rows with their starting and ending column values

    Col1 StartCol2 EndingCol2 StartCol3 EndingCol3 Average(Col4)
    ———– ———– ———– ————- —————- —————–1 1 1 120 210 0.12066
    1 1 2 220 160 0.11704

    Let me know if any one has a question….

    Like

  7. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  11. I have a table DriverLoginDetails with Columns Driver,Action(Login,LogOut),Location
    Can i have following result using First_Valye and Last_Value

    Date,Driver,FirstLogin,FirstLoginLocation,LastLogout,LastLogoutLocation

    Driver and Date combo should be unique

    Like

  12. I have a table DriverLoginDetails with Columns

    Driver,ActionDateTime,Action(Login,LogOut),Location

    Can i have following result using First_Valye and Last_Value

    Date,Driver,FirstLogin,FirstLoginLocation,LastLogout,LastLogoutLocation

    Driver and Date combo should be unique

    Like

  13. I am looking for a way to identify a Date Range. Example: Customer is assigned to Sales Rep 1 on 1/1/2014 and on 3/1/2014 assigned to rep 2. Once a month a new list comes out so the table is:
    Customer Date Rep
    1 1/1/2014 1
    1 2/1/2014 1
    1 3/1/2014 2

    Rep 1 had the customer from 1/1/14 to 3/1/14.
    How can I say use the enddate = the first date assigned to someone else?
    .

    Like

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