Custom Unique Row selection in oracle SQL

Question

Let the table - TableA is as follows,

Row_No	COL1	COL2	COL3	COL4
1	1234	SER1	Y	ABC
2	1234	SER2	N	DEF
3	3456	SER1	Y	XYZ
4	3456	SER2	Y	PQR
5	5678	SER1	N	QAZ
6	5678	SER2	N	SRT

Is it possible to get the result as shown below using oracle sql ?

Row_No	COL1	COL2	COL3	COL4
1	1234	SER1	Y	ABC
3	3456	SER1	Y	XYZ
5	5678	SER1	N	QAZ

The unique row selection rules are:

• Among rows with same value in COL1,
  • Select the row with Y for COL3. (For example, among rows: 1,2; 1st one has Y for COL3)
  • If both rows have same value(Y/N) for COL3 then select one of the row (among 3, 4 -> 3 & 5,6 -> 5 ).

Answer 1

If you sort rows using row_number analytic function and put named conditions into its order by clause, you'd get this:

Sample data:

SQL> with tablea (row_no, col1, col2, col3, col4) as
  2    (select 1, 1234, 'SER1', 'Y', 'ABC' from dual union all
  3     select 2, 1234, 'SER2', 'N', 'DEF' from dual union all
  4     select 3, 3456, 'SER1', 'Y', 'XYZ' from dual union all
  5     select 4, 3456, 'SER2', 'Y', 'PQR' from dual union all
  6     select 5, 5678, 'SER1', 'N', 'QAZ' from dual union all
  7     select 6, 5678, 'SER2', 'N', 'SRT' from dual
  8    ),

Query begins here:

  9  temp as
 10    (select a.*,
 11       row_number() over (partition by col1
 12                          order by case when col3 = 'Y' then 1 else 2 end, row_no) rn
 13     from tablea a
 14    )
 15  select row_no, col1, col2, col3, col4
 16  from temp
 17  where rn = 1;

    ROW_NO       COL1 COL2 COL3 COL4
---------- ---------- ---- ---- ----
         1       1234 SER1 Y    ABC
         3       3456 SER1 Y    XYZ
         5       5678 SER1 N    QAZ

SQL>