SQL & PL/SQL

Hi,
I am trying to filter my output from the query based on some conditions but not able to figure out how. May be I am just overlooking at the issue or is it something tricky.

So, I have a query returning 4 rows of output out of which I need to filter the rows. I have created a table from the result of the query that I need to filter to make it simple. So below is my create table script and values that are obtained from my original query.

CREATE TABLE TEMPACCT
(
  SOURCEKEY           NUMBER,
  FLAG                VARCHAR2(1),
  ITEMID              NUMBER(9)                 ,
  ITEMNAME            VARCHAR2(10)               ,
  ITEMKEY             NUMBER(9)                 
)


Insert into tempacct values (0, 'N', 100, 'ITEM1' , 9647);

Insert into tempacct values (0, 'N', 200, 'ITEM2' , 9648);

Insert into tempacct values (9648, 'N', 100, 'ITEM3' , 9813);

Insert into tempacct values (9647, 'Y', 100, 'ITEM4' , 9812);


SQL> select * from tempacct;

 SOURCEKEY F     ITEMID ITEMNAME      ITEMKEY
---------- - ---------- ---------- ----------
         0 N        100 ITEM1            9647
         0 N        200 ITEM2            9648
      9648 N        100 ITEM3            9813
      9647 Y        100 ITEM4            9812

SQL> 

Tempacct table is the table created from the resultset of my original query.

So from the above output, what I need is 3 rows. The logic to filter out the row is - If any of the row thathas sourcekey that is same as Itemkey in any of the 4 rows and flag is Y then remove the row which have flag =N and only display the one with Falg = Y.

Ok, so, in this case the desired output would be

 SOURCEKEY F     ITEMID ITEMNAME      ITEMKEY
---------- - ---------- ---------- ----------
         0 N        200 ITEM2            9648
      9648 N        100 ITEM3            9813
      9647 Y        100 ITEM4            9812

So here we compared between the first row and the fourth row, and since the sourcekey in fourth row is same as itemkey in first row and Flag is 'Y' for fourth row, we keep 4th row and remove the first row since the flag is 'N'. (and sourcekey is 0. the row that gets removed will always have sourcekey =0) .

SQL> select * from v$version;

BANNER
------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Appreciate your help.