The problem with this approach is that it requires two scans of the base table. Here is an approach that only requires one scan of the table.
;
WITH Chg AS
(
SELECT *, LAG(Grp) OVER(PARTITION BY ID ORDER BY RowID) AS PrevGroup
FROM #tempbase
)
SELECT RowID, ID, Grp
FROM Chg
WHERE Grp IN (1,3)
AND PrevGroup = 2
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA