Remove duplicate based on condition

Question

My table value:

COLUMN1  COLUMN2 COLUMN3
WF1      Email   1640
WF1      Email   1641
WF1      Email   N/A
WF3      Email   N/A

Expected Result:

COLUMN1  COLUMN2 COLUMN3
WF1      Email   1640
WF3      Email   N/A

I need to retrieve all records which column2 = 'Email' and if column1 contains duplicate value, i have to choose the record which column3 <> 'N/A'.

I read tutorial about partition by but still not sure how to get the result.

Any help is appreciated.

CREATE TABLE TABLE1 
    (

     COLUMN1 varchar2(20), 
     COLUMN2 varchar2(20), 
     COLUMN3 varchar2(20) 
    );

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', '1640');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', '1641');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF1', 'Email', 'N/A');

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3)
VALUES
('WF3', 'Email', 'N/A');

Answer 1

Try something like this:

SELECT column1, column2, column3
from(
SELECT column1, column2, column3,
row_number() over (partition BY column1, column2 ORDER BY CASE WHEN column3 = 'N/A' THEN 999999999 ELSE to_number(column3) END ) rn
FROM table1)
WHERE rn = 1

Here is a sqlfiddle