CODEWITHSUNDEEP
sqloracle-database
Frank Moses
Frank Moses

Reputation: 27

select the worst value in partition (hierarchy)

Within each modulo number I want to select the worst class value from class column but only from rows with 'y' in y/n column, then i want to populate this class in each row for each modulo. I have no idea how to implement hierarchy that is:

N is better than O, O is better than P, P is better than W, W is better than S (S is the worst class)

Example:

modulo    y/n    class
1         y      N
1         n      P
1         n      W
1         y      P
2         n      W
2         n      N
3         y      P
3         y      W
3         n      O
2         y      W
4         n      P
4         y      S

and what I would like to achieve:

modulo    y/n    class   worst_class
1         y      N       W
1         n      P       W
1         n      W       W
1         y      P       W
2         n      W
2         n      N
3         y      P       S
3         y      S       S
3         n      O       S
1         y      W       W
4         n      S       P
4         y      P       P

In modulo '1' there are only three values with 'y': N, P and W. The worst value is W, so populate W for every row in modulo '1'. In modulo '2' there are no rows with 'y' so NULL, in modulo '3' there are two values with 'y': P and S. The worst is S. In modulo '4' there is only P with 'y' so populate P in each row.

Upvotes: 0

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

The following query gets what you want as a query:

select m.*, mm.WorstClass
from modulo m left outer join
     (select modulo,
             (case when SUM(case when class = 'S' then 1 else 0 end) > 0 then 'S'
                   when sum(case when class = 'W' then 1 else 0 end) > 0 then 'W'
                   when sum(case when class = 'P' then 1 else 0 end) > 0 then 'P'
                   when sum(case when class = 'O' then 1 else 0 end) > 0 then 'O'
                   when sum(case when class = 'N' then 1 else 0 end) > 0 then 'N'
              end) as WorstClass
      from modulo
      where YN = 'y'
      group by modulo
     ) mm
     on m.modulo = mm.modulo;

Because this is Oracle, the update requires using a correlated subquery:

update modulo m
  set WorstClass = 
      (select (case when SUM(case when class = 'S' then 1 else 0 end) > 0 then 'S'
                    when sum(case when class = 'W' then 1 else 0 end) > 0 then 'W'
                    when sum(case when class = 'P' then 1 else 0 end) > 0 then 'P'
                    when sum(case when class = 'O' then 1 else 0 end) > 0 then 'O'
                    when sum(case when class = 'N' then 1 else 0 end) > 0 then 'N'
               end) as WorstClass
       from modulo mm
       where YN = 'y' and mm.module = m.modulo
      )

This assumes that you have a column for the worst class. If you don't have one, then you need to add one using alter table.

Upvotes: 2

Related Questions