0

How to select the ids following the series, for example, for every id, when condition 1 is 11,12,13 and corresponding condition 2 is null, 14, 16.

The data is as follows

id      condition1  condition2
101       11          ? 
101       13          14    
101       15          16
102       11          ?
102       13          14
102       15          16
102       17          18
103       13          14
103       15          16
104       11          ? 
104       13          14
104       15          16
104       13          14
104       15          16
105       11          ?
105       13          14

expected output

id      condition1  condition2
101       11          ? 
101       13          14    
101       15          16
103       13          14
103       15          16
104       11          ? 
104       13          14
104       15          16
104       13          14
104       15          16

thank you in advance

Nithya Babu
  • 35
  • 5
  • *condition 1 is 11,12,13*? Based on expected answer this should be 11,13,15. Ids with exactly those three rows? Do duplicate rows exist? – dnoeth Oct 12 '21 at 10:59

1 Answers1

1

Edit to match your new logic.

If there are no duplicate rows:

select *
from tab
qualify
   sum(case when condition1 = 11 and condition2 is null
            then 0 -- ignore it
            when condition1 = 13 and condition2 = 14
              or condition1 = 15 and condition2 = 16
            then  1 -- increase for specific rows
            else -1 -- decrease for any other row
        end)
   over (partition by id) = 2

Unfortunatly Windowed Aggregates don't support DISTINCT, but there's a workaroud, check the 1st row only:

select *
from 
 (
   select t.*
     ,row_number() -- check 1st row only
      over (partition by id, condition1, condition2
            order by id) as rn
   from tab as t
 ) as dt
qualify
   sum(case when rn = 1 -- check 1st row only
             and condition1 = 11 and condition2 is null
            then 0 -- ignore it
            when rn = 1 
             and (condition1 = 13 and condition2 = 14
               or condition1 = 15 and condition2 = 16)
            then  1 -- increase for specific rows
            else -1 -- decrease for any other row
        end)
   over (partition by id) = 2
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • yes, there are duplicate values, and for no duplicacy the aove code works. apart from this, I also need to pick those ids which do not have the first case but has other 2 cases, condition1 =11 and condition2 is null, I have edited the table and the expected output. @dnoeth – Nithya Babu Oct 12 '21 at 13:54