Q used to get the ranking value rn ordered by Column1 . Added to PrimaryKey if there are connections in Column1 . C is a recursive CTE that moves from a vertex ordered with rn , an increment of cc for each increasing value of Column2 . It will break out of recursion when cc reaches 10. Finally, get the last 10 lines from C The where clause takes care of when there are no 10 consecutive incremental values.
with Q as ( select PrimaryKey, Column1, Column2, row_number() over(order by Column1, PrimaryKey) as rn from MyTable ), C as ( select PrimaryKey, Column1, Column2, rn, 1 as cc from Q where rn = 1 union all select Q.PrimaryKey, Q.Column1, Q.Column2, Q.rn, case when Q.Column2 > C.Column2 then C.cc + 1 else 1 end from Q inner join C on Q.rn - 1 = C.rn where C.cc < 10 ) select top 10 * from C where 10 in (select cc from C) order by rn desc option (maxrecursion 0)
Version 2 As Martin Smith noted in a comment, the above query has very poor performance. The culprit is the first CTE. The version below uses a table variable to store ranked rows. The primary key directive on rn creates an index that will be used in the connection in the recursive part of the request. Besides the table variable, this does the same as above.
declare @T table ( PrimaryKey int, Column1 datetime, Column2 int, rn int primary key ); insert into @T select PrimaryKey, Column1, Column2, row_number() over(order by Column1, PrimaryKey) as rn from MyTable; with C as ( select PrimaryKey, Column1, Column2, rn, 1 as cc from @T where rn = 1 union all select T.PrimaryKey, T.Column1, T.Column2, T.rn, case when T.Column2 > C.Column2 then C.cc + 1 else 1 end from @T as T inner join C on T.rn = C.rn + 1 where C.cc < 10 ) select top 10 * from C where 10 in (select cc from C) order by rn desc option (maxrecursion 0)