I have the following test data:
| Id | Special |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 0 |
| 9 | 0 |
And I want to compute this:
| Id | Special | LastSeenSpecial |
|---|---|---|
| 1 | 0 | NULL |
| 2 | 0 | NULL |
| 3 | 1 | 3 |
| 4 | 0 | 3 |
| 5 | 0 | 3 |
| 6 | 0 | 3 |
| 7 | 1 | 7 |
| 8 | 0 | 7 |
| 9 | 0 | 7 |
from it.
I have thought about a query snippet that does something like MIN(Id) OVER (PARTITION BY Special ORDER BY Id), but the partitioning comes out wrong and the default value for rows with Id 1 and 2 come out as 1, instead of the required NULL. How can I achieve this? I figure I need to use windowing in some way, but there's no need to use ROW_NUMBER() since Id is already effectively the row number.
What do?
One way is to use a windowed MAX with conditional aggregation Fiddle
SELECT Id,
Special,
MAX(CASE WHEN Special = 1 THEN Id END) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING)
FROM YourTable
ORDER BY Id
You could also use
LAST_VALUE (CASE WHEN Special = 1 THEN Id END) IGNORE NULLS OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING )
If you are on a version of SQL Server supporting IGNORE NULLS here.