drew.allen (7/15/2015)

---

This is essentially an islands and gaps problem with the added wrinkle that the '@@@@@' records are counted in both the islands and the gaps. I used a cross apply to add a column and row to differentiate when these records are acting as an island and a gap.

DECLARE @tbl TABLE (col1 int, col2 int, col3 int, col4 varchar(30))

INSERT @tbl(col1, col2, col3, col4)

VALUES(1, 12, 1, 'This is Test1'),

(2, 12, 2, 'This is Test1'),

(3, 12, 3, 'This is Test3'),

(4, 12, 4, 'This is Test4'),

(5, 12, 5, '@@@@@'),

(6, 12, 1, 'This is another test1'),

(7, 12, 2, 'This is another Test2')

SELECT *

FROM @tbl

;

WITH groups AS (

SELECT *

,ROW_NUMBER() OVER(ORDER BY col1, is_boundry)

-ROW_NUMBER() OVER(PARTITION BY is_boundry ORDER BY col1) AS grp

FROM @tbl

CROSS APPLY (

SELECT CAST(0 AS BIT) AS is_boundry

UNION

SELECT CAST(1 AS BIT)

WHERE col4 = '@@@@@'

) AS c

)

SELECT COL1, COL2, col3, col4, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY col1)

FROM groups

WHERE is_boundry <> 1

ORDER BY col1

Drew