One of the blog posts in the "PCB MS SQL sorting application---adjacent data and the same merge processing" This article should also be used in conjunction with the same technique, to achieve the adjacent data interval value solution
Example:
Original data: Before processing
Calculate interval value: After processing
SQL Code Implementation
DECLARE @table Table(RowNoINT, FlowtechnameVARCHAR( -))INSERT into @table(Rowno,flowtechname)VALUES(1,'Sinking Copper')INSERT into @table(Rowno,flowtechname)VALUES(4,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES(5,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES(6,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES(7,'Sinking Copper')INSERT into @table(Rowno,flowtechname)VALUES(Ten,'Sinking Copper')INSERT into @table(Rowno,flowtechname)VALUES( One,'Sinking Copper')INSERT into @table(Rowno,flowtechname)VALUES( the,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES( -,'Sinking Copper')INSERT into @table(Rowno,flowtechname)VALUES( -,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES( +,'Drilling')INSERT into @table(Rowno,flowtechname)VALUES( A,'Drilling') SELECT * from @tableSELECT min(RowNo) Minrank,MAX(RowNo) maxrank,flowtechname from (SELECTFlowtechname,rowno,row_number () Over(Partition byFlowtechnameOrder byRowNo)-RowNo Diffval from @table) TGROUP byFlowtechname,diffvalORDER by MAX(RowNo)
PCB MS SQL sorting application---SQL Neighbor data interval value solution