Identify the pattern to get desired output

We have below table contains multiple code against the ID and CounterID. Need an output based on code and id.

Condition: Against the ID and CounterID

case 1 : if there is code CI and CO then no record

case 2 : if there is code CI and CO and CI then last record with code CI

case 3 : if there is code CI then last CI

Thanks Rahul

Answer

You can use the analytical function as follows:

select t.* from
(select t.*, row_number() over (partition by sno order by date desc) as rn,
       count(case when code = 'CI' then 1 end) over (partition by sno) as cicount,
       count(case when code = 'CO' then 1 end) over (partition by sno) as cocount 
from your_table t) t
where ( (cocount = 0)
       or not (cicount = 1 and cocount  = 1) 
       or (cocount > cicount ))
  and rn = 1