CODEWITHSUNDEEP
sqloracle-database
Mani
Mani

Reputation: 741

Filter based on condition in WHERE clause

I have a table where I have to pick one of two if it is present. For example if a ID has ACCEPTED and SETTLED , I have to only pick SETTLED else the remaining. Only ACCEPTED/SETTLED always comes as duplicates

Input:

enter image description here

Output:

enter image description here

Query Tried:

   SELECT * FROM TABLE
    WHERE  CASE WHEN "Status" IN ('ACCEPTED','SETTLED') THEN 'SETTLED'
                WHEN "Status" IN ('ACCEPTED') THEN 'ACCEPTED'
 ELSE "Status" END In ('SETTLED','ACCEPTED')

Upvotes: 0

Views: 121

Answers (2)

Janez Kuhar
Janez Kuhar

Reputation: 4266

If your groups are defined by ID and Amount, you could do something like:

SELECT
    t.ID, 
    MAX(t.Status),
    t.Amount
FROM t
GROUP BY t.ID, t.Amount
ORDER BY t.ID

db<>fiddle

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

This is one option (sample data in lines #1 - 7; query begins at line #8). It ranks statuses so that SETTLED comes first, and then the rest of them.

SQL> with test (id, status, amount) as
  2    (select 1, 'ACCEPTED',  13 from dual union all
  3     select 1, 'SETTLED' ,  13 from dual union all
  4     select 2, 'SETTLED' , 155 from dual union all
  5     select 3, 'ACCEPTED', 123 from dual union all
  6     select 4, 'REJECTED', 140 from dual
  7    )
  8  select id, status, amount
  9  from (select id, status, amount,
 10         row_number() over (partition by id
 11                            order by case when status = 'SETTLED' then 1 else 2 end) rn
 12         from test
 13       )
 14  where rn = 1;

        ID STATUS       AMOUNT
---------- -------- ----------
         1 SETTLED          13
         2 SETTLED         155
         3 ACCEPTED        123
         4 REJECTED        140

SQL>

Upvotes: 0

Related Questions