Someone posted a note recently asking about how to detect the changes in a sequence. Say you have a table like this:
DECLARE @t_temp table (ID INT IDENTITY(1,1), Value VARCHAR(20))
insert into @t_temp
select 'Win32' UNION ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win64' union ALL
select 'Win64' union ALL
select 'Win32' union ALL
select 'Win32' union ALL
select 'Win64' UNION ALL
select 'Win64'
SELECT * FROM @t_temp
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
This gives you a simple table and it has these values. Note the first column is just a row counter from the client, the ID and Value columns contain the data.
If you wanted to detect when the Value changes, you’d be looking for the rows with IDs 7, 9, and 11. A simple query gets you that.
select t1.ID, t1.Value
from @t_temp t1
join @t_temp t2
on t2.ID = t1.ID - 1
where t1.Value <> t2.Value
This easily gets those values. However identities aren’t guaranteed to be sequences. What if I deleted row 11 and added another row.
If I add in this code:
INSERT @t_temp SELECT 'Win64'
DELETE FROM @t_temp WHERE ID = 11
And I run the query, I get 7, 9 on my system. A better way to do this is to actually use a subquery to find the previous value in the sequence, not assume it’s one less. Here’s an easy way to do this
select t1.ID, t1.VALUE
from @t_temp t1, @t_temp t2
where t1.id > t2.id
and t1.Value <> t2.VALUE
AND t2.id = (SELECT MAX( id)
FROM @t_temp t3
WHERE t3.id < t1.ID
)
In that subquery, I add a third instance of the table, and I look for the largest ID value that is less than what I’m using as my “current” row in t1. This allows me to find a 10 in t3, and match that to t2 when t1 has the value 12, the next value above 10.
Voice of the DBA 