Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Hi,I have two columns c1 and c2:c1 c21 1 2 03 04 15 16 07 08 09 010 111 012 0what i want is to get column c3 which will enumerate each part of "pairs" between 1 and next 1.so result for C3: c1 c2 c31 1 12 0 23 0 34 1 15 1 16 0 27 0 38 0 49 0 510 1 111 0 212 0 3thank you
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2013-01-10 : 09:56:22
See if this will work for you
SELECT t.c1,t.c2,t.c1-(t2.n-1) AS c3FROM Tbl t CROSS APPLY ( SELECT MAX(c1) AS n FROM Tbl t2 WHERE t2.c1 <= t.c1 AND t2.c2 = 1 ) t2;