How exactly LAST_VALUE() works?

Question

According to definition: The LAST_VALUE() function is a window function that returns the last value in an ordered partition of a result set.

So in my example I need to grab the last ExpirationDate date based on QuoteID.

Using FIRST_VALUE() ordered by QuoteID DESC produces correct result.

So I am expecting using LAST_VALUE() ordered by QuoteID ASC should producer the same result.

However it gives me incorrect result.

Am I missing something?

  DECLARE @Table TABLE ( QuoteID int, PolicyNumber varchar(100), ExpirationDate date)
 INSERT INTO @Table
 (
     QuoteID,
     PolicyNumber,
     ExpirationDate
 )
 VALUES
 (
     1, -- QuoteID - int
     'Pol1', -- PolicyNumber - varchar
     '2019-05-01' -- ExpirationDate - date
 ),
  (
     2, -- QuoteID - int
     'Pol1', -- PolicyNumber - varchar
     '2018-05-01' -- ExpirationDate - date
 )


 SELECT QuoteID, [@Table].ExpirationDate    ,[@Table].PolicyNumber
    ,CAST(FIRST_VALUE(ExpirationDate) OVER (PARTITION BY PolicyNumber ORDER BY QuoteID DESC) AS DATE) as FIRST_VALUE_ExpirationDate --Correct
    ,CAST(LAST_VALUE(ExpirationDate) OVER (PARTITION BY PolicyNumber ORDER BY QuoteID ) AS DATE) as LAST_VALUE_ExpirationDate --Gives Incorrect 
 FROM @Table 
 ORDER BY QuoteID

Answer 1

Per docs on LAST_VALUE:

The default range is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".

And the LAST_VALUE in that range is always the CURRENT ROW

So change to:

  DECLARE @Table TABLE ( QuoteID int, PolicyNumber varchar(5), ExpirationDate date)
 INSERT INTO @Table
 (
     QuoteID,
     PolicyNumber,
     ExpirationDate
 )
 VALUES
 (
     1, -- QuoteID - int
     'Pol1', -- PolicyNumber - varchar
     '2019-05-01' -- ExpirationDate - date
 ),
  (
     2, -- QuoteID - int
     'Pol1', -- PolicyNumber - varchar
     '2018-05-01' -- ExpirationDate - date
 )


 SELECT QuoteID, t.ExpirationDate    ,t.PolicyNumber
    ,FIRST_VALUE(ExpirationDate) 
       OVER (PARTITION BY PolicyNumber 
             ORDER BY QuoteID DESC)  as FIRST_VALUE_ExpirationDate 
    ,LAST_VALUE(ExpirationDate) 
       OVER (PARTITION BY PolicyNumber 
             ORDER BY QuoteID ASC
             RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
     )  as LAST_VALUE_ExpirationDate
 FROM @Table  t
 ORDER BY QuoteID

Which outputs:

QuoteID     ExpirationDate PolicyNumber FIRST_VALUE_ExpirationDate LAST_VALUE_ExpirationDate
----------- -------------- ------------ -------------------------- -------------------------
1           2019-05-01     Pol1         2018-05-01                 2018-05-01
2           2018-05-01     Pol1         2018-05-01                 2018-05-01