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 (https://blog.sqlauthority.com)
15 Comments. Leave new
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
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
Interesting article and good observations.
Thank you
Thank you. That explains it all.
Hi Pinal,
Thank you. It’s good and easily understood article.
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
The PARTITION clause must be deleted.
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….
Is there a way to ignore nulls, and use the last valid value?
Is there a way to ignore nulls, and use the last valid value?
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
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
Is there a way to ignore nulls, and use the last valid value?
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?
.
It seems like the first_value and last_value has same result as below query.
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
min(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID)FstValue,
max(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty