SQL Server query to get first and last value

Question

I have a table with FieldID, ChangeField, OldValue, NewValue and ChangeDate like shown below:

FieldID   ChangeField  OldValue   NewValue   ChangeDate
1         interest     1.5        1.2        2018-05-01 13:00:00
1         interest     1.2        1.3        2018-05-01 14:00:00
1         quantity     2          1          2018-05-01 15:00:00
1         quantity     1          2          2018-05-01 16:00:00
1         quantity     2          3          2018-05-01 17:00:00
2         quantity     10         20         2018-05-01 18:00:00
2         quantity     20         30         2018-05-01 19:00:00

Is it possible to get the first and last changes for every ChangeField per FieldID, for example the below result ?

FieldID ChangeField     OldValue    NewValue    dtChangeDate
1       interest        1.5         1.3     2018-05-01 14:00:00  
--> The original value for interest was 1.5, the last value for interest is 1.3
1       quantity        2           3       2018-05-01 17:00:00  
--> The original value for quantity was 2, the last value for interest is 3
2       quantity        10          30      2018-05-01 19:00:00  
--> The original value for quantity was 10, the last value for interest is 30

Note that ChangeDate is always the latest ChangeDate

Yes, you can use the appropriately named [`FIRST_VALUE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-2017) and [`LAST_VALUE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql?view=sql-server-2017) Functions. — Thom A, May 10 '18 at 14:20

Aura · Accepted Answer · 2018-05-10T14:41:10.617

You can try the following code, I have used FIRST_VALUE() for getting first value in OldValue column and LAST_VALUE() for getting last values of the group for NewValue and dtChangeDate.

    SELECT
    FieldID,
    ChangeField,
    FIRST_VALUE(OldValue) OVER (PARTITION BY
                                    fieldID, ChangeField
                                ORDER BY FieldID
                               )  AS OldValue,
    LAST_VALUE(NewValue) OVER (PARTITION BY
                                   fieldID, ChangeField
                               ORDER BY FieldID
                              )   AS NewValue,
    LAST_VALUE(ChangeDate) OVER (PARTITION BY
                                     fieldID, ChangeField
                                 ORDER BY FieldID
                                ) AS dtChangeDate
    FROM YourTable;

Thank you all. I tried Aureate's solution, and it works ! – faujong May 10 '18 at 15:03 — faujong, May 10 '18 at 15:03

EzLo · Answer 2 · 2018-05-10T14:35:11.973

You can use ROW_NUMBER() to get oldest/newest.

;WITH Boundaries AS
(
    SELECT
        T.FieldID,
        T.ChangeField,
        T.OldValue,
        T.NewValue,
        OldestRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.FieldID,
                T.ChangeField
            ORDER BY
                T.ChangeDate ASC),
        NewestRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.FieldID,
                T.ChangeField
            ORDER BY
                T.ChangeDate DESC)
    FROM
        YourTable AS T
)
SELECT
    T.FieldID,
    T.ChangeField,
    T.OldValue,
    OldValueDate = T.ChangeDate,
    N.NewValue,
    NewValueDate = N.ChangeDate,
FROM
    Boundaries AS T
    INNER JOIN Boundaries AS N ON 
        T.FieldID = N.FieldID AND
        T.ChangeField  = N.ChangeField
WHERE
    T.OldestRanking = 1 AND
    N.NewestRanking = 1

score 0 · Answer 3 · answered May 10 '18 at 14:25

Use first_value() and last_value ()

select distinct * 
from (
  select FieldID, ChangeField  
       first_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) OldValue,
       last_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) NewValue,
       last_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) dtChangeDate
  from table t
) t;

score 0 · Answer 4 · answered May 10 '18 at 14:32

You can try a query like below:

see live demo

;with cte as
( 
    select
    *,
    rn1= row_number() over (partition by FieldID,ChangeField order by ChangeDate asc),
    rn2= row_number() over (partition by FieldID,ChangeField order by ChangeDate desc)
    from
    Tbl
)

select 
    FieldId,
    ChangeField,
    ChangeDate=MAX(ChangeDate),
    Oldvalue= MAX(Case when rn1=1 then Oldvalue end),
    NewValue= MAX(Case when rn2=1 then NewValue end)
from cte 
    where  rn1=1 or rn2=1
group by 
    FieldId,ChangeField

Steven Moseley · Answer 5 · 2022-07-21T17:44:29.013

Using my Scalar Aggregate Reduction technique (per my answer here SQL Query to get column values that correspond with MAX value of another column?) you could very simply perform this in a single grouped query without CTEs or partitions.

Here's the query:

SELECT FieldID, ChangeField,
    MIN(ChangeDate) AS FirstChangeDate,
    SUBSTRING(MIN(CONCAT(ChangeDate, OldValue)), 20) AS FirstOldValue,
    SUBSTRING(MIN(CONCAT(ChangeDate, NewValue)), 20) AS FirstNewValue,
    MAX(ChangeDate) AS LastChangeDate,
    SUBSTRING(MAX(CONCAT(ChangeDate, OldValue)), 20) AS LastOldValue,
    SUBSTRING(MAX(CONCAT(ChangeDate, NewValue)), 20) AS LastNewValue
FROM MyTable
GROUP BY FieldID, ChangeField;

There's also the added benefit of probably 3-4x the performance over the other answers provided above.

More on this technique here: https://www.stevenmoseley.com/high-performance-correlated-aggregate-sql-queries-without-ctes

SQL Server query to get first and last value

5 Answers5