Ignore row if the one of the codes is null

• sqlsdev

  SSC Veteran

  Points: 263

  More actions

  July 21, 2011 at 5:33 pm

  #390031

  I have a data set

  Num RCode Date Code MaxCode

  12 'R1' '12/1/2010' 9 10

  12 NULL '12/1/2010' 10 10

  13 'R8' '12/1/2010' 11 11

  13 NULL '12/1/2010' 8 11

  14 'S1' '10/1/2010' 11 12

  14 'S2' '10/1/2010' 12 12

  15 'A1' '5/1/2011' 10 13

  15 'A1' '5/1/2011' 10 13

  16 'P1' '4/1/2011' 8 13

  16 'NULL '4/1/2011' 8 13

  17 'NULL '1/1/2011' 3 3

  18 'S7' '1/1/2011' 3 9

  19 'S1' '1/1/2011' 9 9

  19 'S2' '1/1/2011' 9 9

  I need below as output...

  Num RCode Date Code MaxCode

  12 R1 12/1/2010 9 10

  13 R8 12/1/2010 11 11

  14 S1 10/1/2010 11 12

  14 S2 10/1/2010 12 12

  15 A1 5/1/2011 10 13

  15 A1 5/1/2011 10 13

  16 P1 4/1/2011 8 13

  17 NULL 1/1/2011 3 3

  18 S7 1/1/2011 3 9

  19 S1 1/1/2011 9 9

  19 S2 1/1/2011 9 9

  Can any one help me.

  Thank you

• sqlsdev

  SSC Veteran

  Points: 263

  More actions

  July 22, 2011 at 7:43 am

  #1357047

  Thanks for you reply.

  I just tried the below sql and it is working as expected.

  select*

  from(

  select*, r = dense_rank() over (partition by num order by case when rcode is not null then 1 else 2 end)

  frommydataset) t

  wheret.r =1

  Thank you agin.