My sample data is like this
drop
table if exists #temp
select
* into #temp
from
(
values
('id100', 'status1', 1),
('id100', 'status2', 2),
('id100', 'status1', 3),
('id100', 'status0', 4),
('id100', 'status2', 5),
('id100', 'status2', 6),
('id100', 'status1', 7),
('id100', 'status1', 8),
('id100', 'status2', 9),
('id101', 'status1', 10),
('id101', 'status2', 11)
) t(id, status, rowNum)
I need TSQL to return immediately preceding rowNum for each id with 'status2' where the status='status1'. I hope the code to return this
id | status | rowNum | value |
---|---|---|---|
id100 | status1 | 1 | |
id100 | status2 | 2 | 1 |
id100 | status1 | 3 | |
id100 | status0 | 4 | |
id100 | status2 | 5 | 3 |
id100 | status2 | 6 | 3 |
id100 | status1 | 7 | |
id100 | status1 | 8 | |
id100 | status2 | 9 | 8 |
id101 | status1 | 10 | |
id101 | status2 | 11 | 10 |
I tried this which did not work
SELECT
t1.id,
t1.status,
t1.rowNum,
(
select
MIN(t2.rowNum)
from
#temp t2
where
t2.id = t1.id
and t2.rowNum < t1.rowNum
and t1.status = 'status2'
) as test
from
#temp t1
3条答案
按热度按时间wribegjk1#
You can use subquery within a case statement to do so:
Query:
Output:
fiddle
You can also use
last_value()
window function orlag()
window function instead of subquery:Query (with last_value()over()):
Output:
Query (with lag()over()):
Output:
fiddle
u5i3ibmn2#
You can use a simple correlated query:
Demo Fiddle
This simply follow the specification to select the first qualifying row based on the predicates for Status.
In order to meet your desired results it's cast to type varchar, if you are happy with
NULL
instead you can omit the cast and theelse
part.This would be optimally helped with a clustered index on Id and rowNum.
l7mqbcuq3#
If you are lucky enough to be running SQL Server 2022, we can just use
last_value()
with theignore nulls
option - as demonstrated in the answer of Kazi Mohammad Ali Nur .In earlier versions, where the option is not available, one approach uses some gaps-and-island technique here.
Islands start with a status 2; we can identify them with a conditional window sum; then, every time a status 2 is met within the island, we retrieve the row num of the corresponding status 1.
An alternative is
apply
- although it might be less efficient, since it requires re-opening the table in the subquery: