Post reply

a good way to compare added value and previous value?

  • January 10, 2008 at 8:47 am

    #182668

    Hi.

    Say I have a table

    ID_1 version Value

    ---------------------------

    1 1 value1

    1 1 value2

    1 2 value1

    1 2 value2

    1 2 value3 --I want to pick up this

    1 3 value1

    1 3 value2

    1 3 value3

    1 3 value4 --and this

    I wanted to pick up all added value (or row) compare to it's previous version. a simple way to do it?

    Thanks

  • January 10, 2008 at 9:06 am

    #766704

    For each GROUP of ID_1 and Version, you want to locate the MAX() Value and return that row ?

    Correct ?

    If so, why are you not requiring the row with ID_1 = 1, Version = 1 and value = "Value2" ?

  • January 10, 2008 at 9:10 am

    #766705

    I don't think I understand your example and question. Why do you want the rows you want, and not also want the one that has a 1 in the second column and "value2" in the third? I don't see the patern.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 10, 2008 at 9:28 am

    #766715

    Try this

    create table mytable(ID_1 int, version int, Value varchar(10))

    insert into mytable(ID_1,version,Value)

    select 1, 1, 'value1' union all

    select 1, 1, 'value2' union all

    select 1, 2, 'value1' union all

    select 1, 2, 'value2' union all

    select 1, 2, 'value3' union all

    select 1, 3, 'value1' union all

    select 1, 3, 'value2' union all

    select 1, 3, 'value3' union all

    select 1, 3, 'value4'

    select a.version,a.Value

    from mytable a

    where exists (select * from mytable b

    where b.version=a.version-1)

    and not exists(select * from mytable c

    where c.version=a.version-1 and c.Value=a.Value)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • January 10, 2008 at 11:13 am

    #766773

    Thanks Mark, it works!

Viewing 5 posts - 1 through 4 (of 4 total)

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