Retain previous value ..

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  November 24, 2009 at 11:09 pm

  #98345

  Hi,

  I have a small requirement. Need output as follows.

  create table test1

  (c1 int,

  c2 int

  )

  insert into test1

  select 1,2

  union all

  select 2,3

  union all

  select 6,8

  union all

  select 7,7

  union all

  select 8,8

  union all

  select 4,9

  union all

  select 9,9

  select c1,c2 from test1

  For the first row the value for c3 should start from 1.

  For the next record, we need compare c1 and c2 columns.

  The logic is as follows

  Case 1

  -------

  If the c1 == c2 then we need to retain the previous c3 value.

  Case 2

  --------

  if c1 != c3 then we need to increment the previous value + 1 and

  display it as C3 column.

  Case 3

  -------

  a.In the first record itself C1 == C2 i.e equal then keep 0 for C3 column in the ouput.

  b. if the first record itself C1 ! = 2 then keep 1 as for C3 column in the ouput.

  For every record we need, keep the previous record intact.

  Expected output

  ===============

  C1 C2 C3

  121

  232

  683

  773 -- bcz C1 and C2 is equal

  883 -- bcz C1 and C2 is equal

  494

  994

  725

  Any help would be greatly appreciated.

  Thanks in advance.

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  November 25, 2009 at 12:32 am

  #1083840

  Dupe post.

  http://www.sqlservercentral.com/Forums/Topic824312-145-1.aspx

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