CODEWITHSUNDEEP
sqloracle-database
ntstha
ntstha

Reputation: 1173

Select row from group which satisfies condition A. If not, give me a row that satisfies condition B

I bring forth an interesting problem that has been bothering me for the past few days. Let's say you have the following data structure:

Col1 |  Col2    | Col3 | Col4
100  | "Val1"   | 0    | 100
100  | "Val2"   | 1    | null
100  | "Val 3"  | 0    | null
101  |  "Val4"  | 0    | null
101  |  "Val5"  | 1    | null
102  |  "Val6"  | 0    | null

I need that one row where Col4!=null. If all rows' Col4 is null then return me a row where Col3=1, but if both Col4 is null and Col3=0, then return me any one row.

So the result set for the above data will look like,

Col1  |  Col2    |  Col3  | Col4
100   |  "Val1"  |   0    |  100
101   |  "Val5"  |   1    | null
102   |  "Val6"  |   0    | null

I know this could be done using analytics function, order them by Col1, Col4 and Col3 and use an analytic function to get the first row in each group but we are using our inhouse ORM that doesn't support analytic function.

Please let me know if this can be done using simple SQL (JOIN, Case, etc).

Edit:

There will only be one row per group where Col4 has non-null value and one row per group where col3 is 1. Also, a single row in the group can satisfy both conditions of having Col4 not null and Col3=1.

Upvotes: 2

Views: 69

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

How about this? Every CONDx CTE solves one condition.

  • COND1 returns rows whose COL4 is not null
  • COND2 returns rows whose COL1 doesn't exist in COND1 result set and has NULLs for COL4 (in that case, count of distinct values = 0) and COL3 = 1
  • COND3 is everything that's left

The final result is union of all those.

SQL> with test (col1, col2, col3, col4) as
  2    (select 100, 'val1', 0, 100  from dual union all
  3     select 100, 'val2', 1, null from dual union all
  4     select 100, 'val3', 0, null from dual union all
  5     select 101, 'val4', 0, null from dual union all
  6     select 101, 'val5', 1, null from dual union all
  7     select 102, 'val6', 0, null from dual
  8    ),
  9  cond1 as
 10    (select col1, col2, col3, col4
 11     From test
 12     where col4 is not null
 13    ),
 14  cond2 as
 15    (select col1, col2, col3, col4
 16     from test t
 17     where t.col1 not in (select col1 from cond1)
 18       and col1 in (select col1
 19                    from test
 20                    group by col1
 21                    having count(distinct col4) = 0
 22                   )
 23       and col3 = 1
 24    ),
 25  cond3 as
 26    (select col1, col2, col3, col4
 27       from test t
 28       where t.col1 not in (select col1 from cond1
 29                            union all
 30                            select col1 from cond2
 31                           )
 32    )
 33  select col1, col2, col3, col4 from cond1
 34  union all
 35  select col1, col2, col3, col4 from cond2
 36  union all
 37  select col1, col2, col3, col4 from cond3
 38  order by col1;

      COL1 COL2       COL3       COL4
---------- ---- ---------- ----------
       100 val1          0        100
       101 val5          1
       102 val6          0

SQL>

Upvotes: 2

Related Questions