Hi There,
I have a particular scenario where i want to filter data base on some condition. consider the following data
with data as (
SELECT 'ROBERT' name, 123 cid, 'med' company from dual union all
SELECT 'ROBERT' name, 123 cid, 'health' company from dual union all
SELECT 'ROBERT' name, 123 cid, 'quest' company from dual union all
SELECT 'SMITH' name, 222 cid, 'metro' company from dual union all
SELECT 'SMITH' name, 222 cid, 'med' company from dual union all
SELECT 'SMITH' name, 222 cid, 'guar' company from dual union all
SELECT 'SMITH' name, 456 cid, 'med' company from dual union all
SELECT 'SOL' name, 235 cid, 'health' company from dual union all
SELECT 'MINA' name, 765 cid, 'Guar' company from dual union all
SELECT 'JOE' name, 111 cid, 'united' company from dual union all
SELECT 'JOE' name, 111 cid, 'Guar' company from dual union all
SELECT 'JOE' name, 111 cid, 'quest' company from dual
)
a person may have 1 or more rows. i want to query and filter the data in such a way that if a particular person has a row where company='health' at lease once then that row should be display.
for example, name = Robert. there are 3 rows with the same name and cid. the second row contain company=health and the others rows for Robert have different values. because at least one row has company=health
then i should display all records for robert cid=123
another example, Smith and cid = 222. there are 3 rows but none of the rows has company='health' so non of the rows for smith will be display and will get filter out
same applies for SOL cid=235. there is only one row with company=health and therefore it should be display.
the other scenario is for company=Guar. these rows should only be display if there is one row available for that patient.
for example. JOE cid=111 has 3 rows with Guar been one of the row. since there are 3 rows, it should not be display.
lets take a look at another example. MINA cid=765 has one row and company=Guar. in this case this person only have one row with value Guar and it should be display.
the final output should be as follow
NAME CID COMPANY
==========================
ROBERT 123 MED
ROBERT 123 HEALTH
ROBERT 123 QUEST
SOL 235 HEALTH
MINA 765 GUAR
I am using oracle 11g. can someone help write a query based on the scenario above?
i started by using analytics function to group together all rows for the same person/cid but couldnt get any further to implement the logic i am looking for
select a.*,
ROW_NUMBER() OVER (PARTITION BY trim(name), cid ORDER BY cid) rnk
FROM data