How to capture changes?

  • Hi,

    For example, I have a table like this:

    IDvalue

    15

    25

    37

    47

    57

    67

    79

    89

    95

    105

    117

    ......

    How can I find out the IDs where the value changes? in this case, they would be 3, 7, 9 and 11.

    Thanks

  • try:

    select id

    from mytable

    group by id

    having min(val) != max(val)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks. It doesn't work though. The ID column is an identity column...

    Eugene Elutin (6/18/2010)


    try:

    select id

    from mytable

    group by id

    having min(val) != max(val)

  • declare @t_temp table (ID int, Value int)

    insert into @t_temp

    select 1, 5 union

    select 2, 5 union

    select 3, 7 union

    select 4, 7 union

    select 5, 7 union

    select 6, 7 union

    select 7, 9 union

    select 8, 9 union

    select 9, 5 union

    select 10, 5 union

    select 11, 7

    select t1.ID, t1.Value

    from @t_temp t1

    join @t_temp t2

    on t2.ID = t1.ID - 1

    where t1.Value <> t2.Value

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks!!! This is it.

    bteraberry (6/18/2010)


    declare @t_temp table (ID int, Value int)

    insert into @t_temp

    select 1, 5 union

    select 2, 5 union

    select 3, 7 union

    select 4, 7 union

    select 5, 7 union

    select 6, 7 union

    select 7, 9 union

    select 8, 9 union

    select 9, 5 union

    select 10, 5 union

    select 11, 7

    select t1.ID, t1.Value

    from @t_temp t1

    join @t_temp t2

    on t2.ID = t1.ID - 1

    where t1.Value <> t2.Value

  • Be careful about gaps in the identity column,

  • Steve Jones - Editor (6/18/2010)


    Be careful about gaps in the identity column,

    Thanks Steve... This is exactly what I am thinking now. Let's say there's another column in the table

    ID Value Model

    1 5 win32

    2 5 win32

    3 7 win32

    4 7 win32

    5 7 win32

    6 7 win32

    7 9 win64

    8 9 win64

    9 5 win32

    10 5 win32

    11 7 win64

    ....

    I only want to find out changes on model "win32", in this case, the IDs would be 3,9.... plus, the ID column might have gaps. What's the solution?

    Thanks

  • Do you only care about changes in the platform? You need to use a subquery to find the previous value. Here's a basic query. Not sure it's what you want, but this is the idea.

    declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))

    insert into @t_temp

    select 1, 5, 'Win32' union

    select 2, 5, 'Win32' union

    select 3, 7, 'Win32' union

    select 4, 7, 'Win32' union

    select 5, 7, 'Win32' union

    select 6, 7, 'Win32' union

    select 7, 9, 'Win64' union

    select 8, 9, 'Win64' union

    select 9, 5, 'Win32' union

    select 10,5, 'Win32' union

    select 12,7, 'Win64' UNION

    select 13,7, 'Win64'

    select t1.ID, t1.VALUE, t1.Value2

    from @t_temp t1, @t_temp t2

    where t1.id > t2.id

    and t1.Value2 <> t2.VALUE2

    AND t2.id = (SELECT MAX( id)

    FROM @t_temp t3

    WHERE t3.id < t1.ID

    )

  • Thanks Steve.

    This is what I got from abdshall@Technet, and it works:

    DECLARE @t TABLE(ID INT,Value INT,Model VARCHAR(50))

    INSERT INTO @t

    SELECT 1, 5, 'win32' UNION ALL

    SELECT 2, 5, 'win32' UNION ALL

    SELECT 3, 7, 'win32' UNION ALL

    SELECT 4, 7, 'win32' UNION ALL

    SELECT 5, 7, 'win32' UNION ALL

    SELECT 6, 7, 'win32' UNION ALL

    SELECT 7, 9, 'win64' UNION ALL

    SELECT 8, 9, 'win64' UNION ALL

    SELECT 9, 5, 'win32' UNION ALL

    SELECT 10, 5, 'win32' UNION ALL

    SELECT 11, 7, 'win64'

    ;WITH CTE AS

    (

    SELECT ID,Value,Model,ROW_NUMBER() OVER(ORDER BY ID) -

    ROW_NUMBER() OVER(ORDER BY Value,id) AS Row2

    FROM @t

    WHERE Model = 'Win32'

    )

    SELECT MIN(ID) AS ID,Value,Model

    FROM CTE

    WHERE Row2 <> 0

    GROUP BY value,Row2,Model

    ORDER BY ID

    Steve Jones - Editor (6/18/2010)


    Do you only care about changes in the platform? You need to use a subquery to find the previous value. Here's a basic query. Not sure it's what you want, but this is the idea.

    declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))

    insert into @t_temp

    select 1, 5, 'Win32' union

    select 2, 5, 'Win32' union

    select 3, 7, 'Win32' union

    select 4, 7, 'Win32' union

    select 5, 7, 'Win32' union

    select 6, 7, 'Win32' union

    select 7, 9, 'Win64' union

    select 8, 9, 'Win64' union

    select 9, 5, 'Win32' union

    select 10,5, 'Win32' union

    select 12,7, 'Win64' UNION

    select 13,7, 'Win64'

    select t1.ID, t1.VALUE, t1.Value2

    from @t_temp t1, @t_temp t2

    where t1.id > t2.id

    and t1.Value2 <> t2.VALUE2

    AND t2.id = (SELECT MAX( id)

    FROM @t_temp t3

    WHERE t3.id < t1.ID

    )

  • Hi all,

    Are you sure that the previous solution works successfully?

    I could not understand why we need to set the win32 in the WHERE clause of the CTE

    I think Steve has a better solution suggesstion.

    But I would merge the CTE part of the previous solution to prevent gap problems with the Steve's solution.

  • Here is my code suggestion :

    with cte as (

    select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges

    )

    select t1.*, t2.*

    from cte t1

    full join cte t2 on t1.rn = t2.rn + 1

    where t1.model <> t2.model

  • Hi, Erapler

    I used your solution, but did not get what I really want, did I miss anything? Thanks

    DECLARE @t TABLE(ID INT,Value INT,Model VARCHAR(50))

    INSERT INTO @t

    SELECT 1, 5, 'win32' UNION ALL

    SELECT 2, 5, 'win32' UNION ALL

    SELECT 3, 7, 'win32' UNION ALL

    SELECT 4, 7, 'win32' UNION ALL

    SELECT 5, 7, 'win32' UNION ALL

    SELECT 6, 7, 'win32' UNION ALL

    SELECT 7, 9, 'win64' UNION ALL

    SELECT 8, 9, 'win64' UNION ALL

    SELECT 9, 5, 'win32' UNION ALL

    SELECT 10, 5, 'win32' UNION ALL

    SELECT 11, 7, 'win64'

    ;with cte as (

    select *, rn = ROW_NUMBER() OVER (Order By Id) from @t

    )

    select t1.*, t2.*

    from cte t1

    full join cte t2 on t1.rn = t2.rn + 1

    where t1.model <> t2.model

    The result is:

    IDValueModelrnIDValueModelrn

    79win64767win326

    95win32989win648

    117win6411105win3210

    Eralper (6/21/2010)


    Here is my code suggestion :

    with cte as (

    select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges

    )

    select t1.*, t2.*

    from cte t1

    full join cte t2 on t1.rn = t2.rn + 1

    where t1.model <> t2.model

  • declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))

    insert into @t_temp

    select 1, 5, 'Win32' union

    select 2, 5, 'Win32' union

    select 3, 7, 'Win32' union

    select 4, 7, 'Win32' union

    select 5, 7, 'Win32' union

    select 6, 7, 'Win32' union

    select 7, 9, 'Win64' union

    select 8, 9, 'Win64' union

    select 9, 5, 'Win32' union

    select 10,5, 'Win32' union

    select 12,7, 'Win64' union

    select 13,7, 'Win64';

    with cteTemp(ID, Value, RowNum)

    as

    (

    select ID,

    Value,

    ROW_NUMBER() OVER(ORDER BY ID)

    from @t_temp

    where Value2 = 'Win32'

    )

    select cc.ID

    from cteTemp cc

    join cteTemp cp

    on cp.RowNum = cc.RowNum - 1

    where cc.Value <> cp.Value

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks. Got it. Basically add a column rownumber as identity.

    bteraberry (6/21/2010)


    declare @t_temp table (ID int, Value INT, Value2 VARCHAR(20))

    insert into @t_temp

    select 1, 5, 'Win32' union

    select 2, 5, 'Win32' union

    select 3, 7, 'Win32' union

    select 4, 7, 'Win32' union

    select 5, 7, 'Win32' union

    select 6, 7, 'Win32' union

    select 7, 9, 'Win64' union

    select 8, 9, 'Win64' union

    select 9, 5, 'Win32' union

    select 10,5, 'Win32' union

    select 12,7, 'Win64' union

    select 13,7, 'Win64';

    with cteTemp(ID, Value, RowNum)

    as

    (

    select ID,

    Value,

    ROW_NUMBER() OVER(ORDER BY ID)

    from @t_temp

    where Value2 = 'Win32'

    )

    select cc.ID

    from cteTemp cc

    join cteTemp cp

    on cp.RowNum = cc.RowNum - 1

    where cc.Value <> cp.Value

  • Hi pcgm,

    My code responds to changes in model

    If you want to respond to changes in value column please change the WHERE clause as follows

    t1.value <> t2.value --t1.model <> t2.model

    I mean

    with cte as (

    select *, rn = ROW_NUMBER() OVER (Order By Id) from FindChanges

    )

    select t1.*, t2.*

    from cte t1

    full join cte t2 on t1.rn = t2.rn + 1

    where t1.value <> t2.value

    I hope I'm not missing something too.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply