Search code examples
sqlpostgresqlplsqlplsqldeveloper

case statement with in group by or partition by clause

Customer   Decision      req_date   
   A       Approved     2017-06-13
   A       Approved     2017-06-13
   A       Pending      2017-06-13
   B       Pending      2017-10-13
   B       Approved     2017-06-13
   C       Pending      2017-07-14

For a given customer ID,

If the decision is Approved, retain only the approved row for the customer .If the customer doesn't have any Approved decision then retain all the rows of the customer.

expecting output

Customer   Decision      req_date   
   A       Approved     2017-06-13
   A       Approved     2017-05-13
   B       Approved     2017-06-13
   C       Pending      2017-07-14
Solution

  • I would use or:

    select t.*
from t
where t.decision = 'Approved' or
      not exists (select 1
                  from t t2
                  where t2.Customer = t.Customer and t2.decision = 'Approved'
                 );