0

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

1 Answer 1

2

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;
1
  • Perfect thanks. Client is migrating to more recent version but in the meantime I still have to work with the older feature set. Oct 12, 2020 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.