Conditional Checking (Waterfall order)

Question

Here is the scenario: Every employee belongs to multiple categories based on some conditions. These categories have an order. While checking the category for an employee, if he belongs to Category 1 we should not check for other categories. If he DOES NOT belongs to Category 1 and he belongs to category 2 then we should not check the other categories and so on. How do we write that in the SELECT clause?

I am unable to think about a query to begin with.

If categories are based on distinct columns, then look into `coalesce()`. If categories are based on mulitple conditions, then look into `case expression`. — Isolated, Mar 06 '23 at 20:09

score 0 · Answer 1 · answered Mar 06 '23 at 20:17

It would be better if you posted test case; without it, we have to figure it out on our own, so - here's my attempt which doesn't make much sense (join with category isn't necessary because simple MIN aggregate function along with GROUP BY would do, but OK, never mind that - this is just example).

Sample data:

SQL> with
  2  category (cat_id, name, c_order) as
  3    -- lower C_ORDER is better
  4    (select 1, 'Cat 1', 1 from dual union all
  5     select 2, 'Cat 2', 2 from dual union all
  6     select 3, 'Cat 3', 3 from dual
  7    ),
  8  employee (emp_id, name, cat_id) as
  9    (select 100, 'Little', 2 from dual union all  --> this
 10     select 100, 'Little', 3 from dual union all
 11     select 200, 'Foot'  , 1 from dual union all  --> this
 12     select 200, 'Foot'  , 3 from dual
 13    ),

Join tables and rank rows by column that represents category "order"; then fetch rows that ranked as the highest:

 14  temp as
 15    (select e.emp_id, e.name emp_name, c.name cat_name,
 16       row_number() over (partition by e.emp_id order by c.c_order) rn
 17     from employee e join category c on c.cat_id = e.cat_id
 18    )
 19  select t.emp_id, t.emp_name, t.cat_name
 20  from temp t
 21  where rn = 1;

      EMP_ID EMP_NAME   CAT_NAME
------------ ---------- ----------
         100 Little     Cat 2
         200 Foot       Cat 1

SQL>

If that's not "it", do as I said at the beginning: post test case and desired result.

Conditional Checking (Waterfall order)

1 Answers1