Using LAST_VALUE

Question

Post reply

Using LAST_VALUE

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

preacha SSC Veteran Points: 236 More actions · Answer 1

Hi I basically want to return all of the ticketid's that have a reportdate that was changed

ie...

TICKETID, REPORTDATE

a,17/04/15

a,19/02/14

b,12/02/15

c,01/01/15

c,01/02/15

should return

a,17/04/15

a,19/02/14

c,01/01/15

c,01/02/15

Kinda like the PREVIOUS() function in srss... example was to highlight the field that was changed....

=iif(Previous(Fields!TICKETID.Value) = Fields!TICKETID.Value and Previous(Fields!REPORTDATE.Value) <> Fields!REPORTDATE.Value, "Red","Black")

Thanks heaps guys 🙂 Hope this makes sense!

chiesa.alberto Old Hand Points: 300 More actions · Answer 2

preacha (4/16/2015)
Hi I basically want to return all of the ticketid's that have a reportdate that was changed
ie...
TICKETID, REPORTDATE
a,17/04/15
a,17/04/15
a,19/02/14
b,12/02/15
b,12/02/15
b,12/02/15
c,01/01/15
c,01/02/15
should return
a,17/04/15
a,19/02/14
c,01/01/15
c,01/02/15
Kinda like the PREVIOUS() function in srss... example was to highlight the field that was changed....
=iif(Previous(Fields!TICKETID.Value) = Fields!TICKETID.Value and Previous(Fields!REPORTDATE.Value) <> Fields!REPORTDATE.Value, "Red","Black")
Thanks heaps guys 🙂 Hope this makes sense!

Have you tried something along this lines?

If you need only one row with previous and current values you can get rid of the NEXTDATE expression.

WITH DATA AS (

SELECT 'a' as TICKETID, '20150417' as REPORTDATE

UNION ALL SELECT 'a', '20150417'

UNION ALL SELECT 'a', '20140219'

UNION ALL SELECT 'b', '20150212'

UNION ALL SELECT 'c', '20150101'

UNION ALL SELECT 'c', '20150201'

), COMPARISON AS (

SELECT TICKETID, REPORTDATE,

LAG(REPORTDATE) OVER(PARTITION BY TICKETID ORDER BY REPORTDATE DESC) AS PREVIOUSDATE,

LAG(REPORTDATE) OVER(PARTITION BY TICKETID ORDER BY REPORTDATE) AS NEXTDATE

FROM DATA

)

SELECT *

FROM COMPARISON

WHERE REPORTDATE <> PREVIOUSDATE OR

REPORTDATE <> NEXTDATE

ORDER BY TICKETID, REPORTDATE DESC;

samot-dwarf SSC Eights! Points: 984 More actions · Answer 3

I'm lazy and use FIRST_VALUE with ORDER BY x DESC instead of this extra long LAST-VALUE syntax.

Of course only if I don't need both (FIRST and LAST) because it would have to sort it twice otherwise.

Thomas Franz Hall of Fame Points: 3894 More actions · Answer 4

@topic:

- you should never use FIRST_VALUE without "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", otherwise it will always spill out to TempDB -> slow

- for this reason I prever to use FIRST_VALUE with the ROWS-Clause with descending ORDER above LAST_VALUE

@preacha

- Use

LAG (AU.REPORTDATE) OVER (PARTITION BY AU.TICKETID ORDER BY AU.EAUDITTIMESTAMP) as PREV_RPTDATE,

LEAD (AU.REPORTDATE) OVER (PARTITION BY AU.TICKETID ORDER BY AU.EAUDITTIMESTAMP) as NEXT_RPTDATE,

- remove the LEAD / LAG for the TicketID.

- change your outer WHERE to check for NULL / NOT NULL in prev / next_rptdate

- ALWAYS use brackets, if you are combining AND and OR conditions in a WHERE - I have seen too much errors because someone (including me) used them in the wrong order (or added an additional condition) and broke the statement. Beside of this its always harder to understand without brackets.

- Except you are using IN MEMORY Tables or binary / Case sensitive Collations, you could remove the UPPER()-calls (will save some CPU cycles)

- Read the part about maybe-parameters in http://www.sqlservercentral.com/articles/T-SQL/121906/%5B/url%5D and try to avoid them (outer WHERE)

God is real, unless declared integer.

tamirgerber Valued Member Points: 69 More actions · Answer 5

I had the same story with AVG. It was returning an average of the column values between the first and the current row.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 6

bhupendra.baraiya (4/16/2015)
we can achieve the same output without adding the rows Cluse
...
, LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD
...

I like this observation (emphasis added). Is there a "gotcha" with this approach? Am I missing something?

amd.repetto Ten Centuries Points: 1316 More actions · Answer 7

The only thing I must disagree is with the first statement "Windowing functions are the best thing since sliced bread when it comes to T-SQL".. they only will be the best when Microsoft adds something to easily find the first or last value that is NOT NULL.

I had to face a problem weeks ago when the first_value was returning me nulls when I knew that it shouldn't.. after think and think I realized that it was returnin null just for a simple reason.. null was the frist value on the list to pick.. so after making a little trick on order within over clause to leave nulls out of the pick it finally worked. After googling a while I noticed that oracle has an option to do this, my disappointment was when I realized that sql did not have it.

Anyway I think so far windowed functions are very useful, but they still need work on them to add better features.

A. Mauricio Repetto
ML Engineer

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 8

EDIT: This is an answer to the question about using LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID, ORDER BY CustomerID)

There are a couple. First, the performance could be bad. ROWS will generally perform better than the default.

Also, the ORDER BY is not specific to how you want the rows to sort. When you order by CustomerID, which is the first row? Which is the last row? The results will be dependent on how the results are returned. If you specify an different ORDER BY for the query, that can change the results of FIRST_VALUE and LAST_VALUE. In this case, it is working because it just happens to return the rows in the order you want. If it didn't then this wouldn't work.

I am not sure why there is so much push back about using the frame.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 9

amd.repetto (6/3/2016)
The only thing I must disagree is with the first statement "Windowing functions are the best thing since sliced bread when it comes to T-SQL".. they only will be the best when Microsoft adds something to easily find the first or last value that is NOT NULL.
I had to face a problem weeks ago when the first_value was returning me nulls when I knew that it shouldn't.. after think and think I realized that it was returnin null just for a simple reason.. null was the frist value on the list to pick.. so after making a little trick on order within over clause to leave nulls out of the pick it finally worked. After googling a while I noticed that oracle has an option to do this, my disappointment was when I realized that sql did not have it.
Anyway I think so far windowed functions are very useful, but they still need work on them to add better features.

I agree. I was hoping to see new functionality in SQL Server 2016.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 10

samot-dwarf (4/20/2015)
I'm lazy and use FIRST_VALUE with ORDER BY x DESC instead of this extra long LAST-VALUE syntax.
Of course only if I don't need both (FIRST and LAST) because it would have to sort it twice otherwise.

Be sure to watch out for performance issues if you are working with a large dataset.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 11

bhupendra.baraiya (4/16/2015)
we can achieve the same output without adding the rows Cluse
SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate AS DATE) AS OrderDate
, TotalDue
, LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate AS DATE) AS OrderDate
, TotalDue
, First_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID

Don't count on this to always work! The results will change depending on the order the rows are returned. Since CustomerID is not unique, the rows could be returned in a different order and then the results will be different.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Thomas Franz Hall of Fame Points: 3894 More actions · Answer 12

... Furthermore without the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW it would be slow because with the default RANGE-parameter it has to spill out to TempDB.

God is real, unless declared integer.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 13

Kathi Kellenberger (6/3/2016)
EDIT: This is an answer to the question about using LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID, ORDER BY CustomerID)
Also, the ORDER BY is not specific to how you want the rows to sort. When you order by CustomerID, which is the first row? Which is the last row? The results will be dependent on how the results are returned. If you specify an different ORDER BY for the query, that can change the results of FIRST_VALUE and LAST_VALUE. In this case, it is working because it just happens to return the rows in the order you want.

Thanks for the reply. I knew I was missing something and that's why I asked. Seems obvious now. 🙂