Using LAST_VALUE

  • Comments posted to this topic are about the item Using LAST_VALUE

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

  • Hi I'm trying to apply this to an SSRS report I am working on, which compares dates from previous rows. I previously just used LAG/LEAD but rows aren't showing as required. It's pretty much like trying to compare using the Previous() function in SSRS but I can't use this to filter out values when the parameter @ReportDateChanged is checked.

    Below is my code. The area in bold is what I am trying to play around with.

    At the moment it just shows some report dates that have been changed but not all....

    WITH TICKET_TBL AS

    (

    SELECT AU.TICKETID,

    TICKET.DESCRIPTION,

    AU.JHG_RAILPRIORITY,

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

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

    LAG (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_DEFECT,

    LEAD (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as NEXT_DEFECT,

    AU.REPORTDATE,

    AU.TARGETSTART,

    AU.TARGETFINISH,

    AU.EAUDITUSERNAME,

    AU.EAUDITTIMESTAMP,

    AU.EAUDITTYPE,

    TICKET.JHG_WORKGROUP

    FROM MAXIMO.AU_TICKET AU

    LEFT OUTER JOIN MAXIMO.TICKET ON TICKET.TICKETID = AU.TICKETID

    WHERE AU.SITEID = 'CRN' and AU.TICKETID like upper(@DefectNo) + '%' and AU.EAUDITUSERNAME like upper(@UserName) + '%'

    )

    SELECT TICKETID,

    DESCRIPTION,

    JHG_RAILPRIORITY,

    PREV_RPTDATE,

    NEXT_RPTDATE,

    REPORTDATE,

    TARGETSTART,

    TARGETFINISH,

    EAUDITUSERNAME,

    EAUDITTIMESTAMP,

    EAUDITTYPE,

    JHG_WORKGROUP

    FROM TICKET_TBL

    WHERE @ReportDateChanged = 1 and PREV_DEFECT = NEXT_DEFECT and PREV_RPTDATE <> NEXT_RPTDATE

    OR @ReportDateChanged = 0

    ORDER BY TICKETID, EAUDITTIMESTAMP

    Any help would be appreciated.

  • Thx for the article. Very interesting.

  • Hi,

    You Can achieve the same Result by below statement for FIRST_VALUE and LAST_VALUE

    ------------------------------------------------FIRST_VALUE-------------------------------------------------------

    SELECT

    CustomerID

    , SalesOrderID

    , CAST(OrderDate AS DATE) AS OrderDate

    , TotalDue

    , FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TD

    FROM

    Sales.SalesOrderHeader

    ORDER BY

    CustomerID

    , SalesOrderID;

    ------------------------------------------------LAST_VALUE-------------------------------------------------------

    SELECT

    CustomerID

    , SalesOrderID

    , CAST(OrderDate AS DATE) AS OrderDate

    , TotalDue

    , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TD

    FROM

    Sales.SalesOrderHeader

    ORDER BY

    CustomerID

    , SalesOrderID;

  • Very interesting Post.

    Basically, the "expected" behaviour for LAST_VALUE is achievable without ROWS clause, just specifying DESC in the ORDER BY clause of the FIRST_VALUE aggregate.

    SELECT

    CustomerID

    , SalesOrderID

    , CAST(OrderDate AS DATE) AS OrderDate

    , TotalDue

    , FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID DESC) AS TD

    FROM

    Sales.SalesOrderHeader

    ORDER BY

    CustomerID

    , SalesOrderID;

    Anyone has considerations about what would be better between FIRST_VALUE with DESC or LAST_VALUE with ROWS? I would be interested in any experts consideration about it.

    Thanks for the article.

    Alberto Chiesa

  • Hi chiesa,

    Your Suggestion will also work... But

    As per Performance Tuning matter,

    in your Solution.

    First of all the Data will be Sorted in DESC Order and then It will Apply FIRST_VALUE from ORDERED Value..

    So here data is very less.. But in ACTUAL world ther may be thousand and Lacks of records... So at that time your suggestion take more time to achieve as compare to

    LAST_VALUE(Col3) OVER (Partition by Col1 order by anyCol2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

    Or simple

    LAST_VALUE(Col3) OVER (Partition by Col1 order by anyCol2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

    ------------

    Hope you will get an idea..

    Share your Observation

  • I believe the following lines have been overlooked...

    "...However, if you’re after the last row in the partition, using

    the LAST_VALUE function with the default frame won’t give you what you want because

    the last row in the default frame is the current row. So with the LAST_VALUE, you need to

    be explicit about the window frame in order to get what you are after. And if you need

    an element from the last row in the partition, the second delimiter in the frame should be

    UNBOUNDED FOLLOWING."

    Chapter 5 Grouping and Windowing - Pag. 180

    Exam 70-641 Querying SQL Server 2012, Itzik Ben-Gan, Dejan Sarka, Ron Talamage

  • Very good article. The behavior of LAST_VALUE() would seem unintuitive without considering the frame. Thanks for highlighting this potential "gotcha".

    I tried to apply the ROWS/RANGE clause to other windowing functions, but it is not implemented across the board. What is the rule for when the frame is available?

  • ROWS and RANGE are only supported with Window Aggregates (for example, running totals) and FIRST_VALUE and LAST_VALUE.

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

  • Any ideas guys?

  • 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

  • *bump* 🙂

    preacha (4/13/2015)


    Hi I'm trying to apply this to an SSRS report I am working on, which compares dates from previous rows. I previously just used LAG/LEAD but rows aren't showing as required. It's pretty much like trying to compare using the Previous() function in SSRS but I can't use this to filter out values when the parameter @ReportDateChanged is checked.

    Below is my code. The area in bold is what I am trying to play around with.

    At the moment it just shows some report dates that have been changed but not all....

    WITH TICKET_TBL AS

    (

    SELECT AU.TICKETID,

    TICKET.DESCRIPTION,

    AU.JHG_RAILPRIORITY,

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

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

    LAG (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_DEFECT,

    LEAD (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as NEXT_DEFECT,

    AU.REPORTDATE,

    AU.TARGETSTART,

    AU.TARGETFINISH,

    AU.EAUDITUSERNAME,

    AU.EAUDITTIMESTAMP,

    AU.EAUDITTYPE,

    TICKET.JHG_WORKGROUP

    FROM MAXIMO.AU_TICKET AU

    LEFT OUTER JOIN MAXIMO.TICKET ON TICKET.TICKETID = AU.TICKETID

    WHERE AU.SITEID = 'CRN' and AU.TICKETID like upper(@DefectNo) + '%' and AU.EAUDITUSERNAME like upper(@UserName) + '%'

    )

    SELECT TICKETID,

    DESCRIPTION,

    JHG_RAILPRIORITY,

    PREV_RPTDATE,

    NEXT_RPTDATE,

    REPORTDATE,

    TARGETSTART,

    TARGETFINISH,

    EAUDITUSERNAME,

    EAUDITTIMESTAMP,

    EAUDITTYPE,

    JHG_WORKGROUP

    FROM TICKET_TBL

    WHERE @ReportDateChanged = 1 and PREV_DEFECT = NEXT_DEFECT and PREV_RPTDATE <> NEXT_RPTDATE

    OR @ReportDateChanged = 0

    ORDER BY TICKETID, EAUDITTIMESTAMP

    Any help would be appreciated.

  • This is Cool... 🙂

  • Preacha, probably you could spend some time giving more information about the source data and the desired result.

    Telling "here is a query that doesn't work as I want it to", is pretty much useless, IMO.

    Even if someone is wanting to help.

    Bye.

  • LAG and LEAD functions support window partition, try adding the "PARTITION BY" to your LAG and LEAD functions to get the correct data,

    i.e.

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

    or

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

    When you restrict those functions to the correct partition of data, the CTE will return the correct set...

    More than this, it is difficult to help due to the above mentioned lack of info provided...

Viewing 15 posts - 1 through 15 (of 27 total)

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