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 ).
CodePudding user response:
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>