Another question about finding the last time a value changed

Question

I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns.

| TimeStartTz             | RowNumber | RateID      |
|-------------------------|-----------|-------------|
| 2018-05-07 07:00:00.000 | 45        | 1           |
| 2018-04-09 07:00:00.000 | 43        | 1           |
| 2018-03-26 07:00:00.000 | 42        | 1           |
| 2018-03-19 07:00:00.000 | 41        | 1           | <-- want this row
| 2018-03-05 07:00:00.000 | 2         | 2           |
| 2018-02-26 07:00:00.000 | 1         | 2           |
| 2018-02-19 07:00:00.000 | 40        | 1           |
| 2018-02-12 07:00:00.000 | 39        | 1           |
| 2018-01-29 06:45:00.000 | 38        | 1           |
| 2018-01-22 06:45:00.000 | 37        | 1           |

I want to get the row of the most recent change in RateID or the first row if there is no change in the set, i.e. in the example the row with RowNumber 41. I tried partitioning by RateID but it obviously doesn't account for contiguous sections:

    ROW_NUMBER() OVER (PARTITION BY pq.[RateID] ORDER BY pq.[TimeStart]) RowNumber

Answer 1

I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x).

So your solution would require the a self-join like this:

WITH CTE_MostRecentChange
AS
(
    SELECT 
        TimeStartTz, 
        RateID,
        ROW_NUMBER() OVER (ORDER BY TimeStartTz DESC) AS RowNumber
    FROM MostRecentChange
)
SELECT TOP(1) 
    CTE_1.RowNumber, 
    CTE_1.TimeStartTz, 
    CTE_1.RateID
FROM CTE_MostRecentChange CTE_1
    LEFT JOIN CTE_MostRecentChange CTE_2
        ON CTE_1.RowNumber = CTE_2.RowNumber - 1
WHERE CTE_1.RateID <> CTE_2.RateID
    OR CTE_2.RowNumber IS NULL
ORDER BY CTE_1.TimeStartTz DESC;