0

At Oracle I would like to filter below TABLE as

COLUMNA COLUMNB COLUMNC
19 AAA PRIMARY
20 AAA PRIMARY
8 AAA SECONDARY
7 AAA SECONDARY
7 AAA PRIMARY
8 AAA SECONDARY
9 AAA SECONDARY

my expected output is

COLUMNA COLUMNB COLUMNC
19 AAA PRIMARY
20 AAA PRIMARY
7 AAA PRIMARY
9 AAA SECONDARY

Logic is Group by COLUMNA and COLUMNB (please evaluate below conditions on grouping.)

  • if columnc candidate record (grouped by COLUMNA and COLUMNB) includes only PRIMARY take PRIMARY.
  • if columnc candidate record (grouped by COLUMNA and COLUMNB) includes only SECONDARY take SECONDARY.
  • if columnc candidate records (grouped by COLUMNA and COLUMNB) include PRIMARY and SECONDARY set, take PRIMARY.
  • if columnc candidate records (grouped by COLUMNA and COLUMNB) include duplicate SECONDARY set skip record.
  • imagine this row has 100 columns so I need to fetch row by itself. MAX MIN wont work here.

I have used couple of row_number() functions and where not exists but went into oblivion.

Improve this question
5
  • Please explain the logic. Commented May 7, 2021 at 13:08
  • Why is 9 in the result set? Commented May 7, 2021 at 13:19
  • edited condition. Because it has to be there. If only secondary I have with the column, it should be included @GordonLinoff Commented May 7, 2021 at 13:25
  • The COLUMNA 8 value has 2 SECONDARY and it's not on your expected output. Are you clear on what you want? Why do you say the logic is un COLUMNA and COLUMNB if COLUMNB always have the same value? Commented May 7, 2021 at 13:30
  • yes @JaimeDrq. I dont need that rows, it has to be skipped. Because I dont have candidate for primary there and double secondary is the problem. Commented May 7, 2021 at 13:32

3 Answers 3

Reset to default
1

Maybe this query is what you are looking for. As I said in my comment, 8 should be also included based on your description

with your_data as ( select 19 as columna, 'AAA' as columnb, 'PRIMARY' as columnc from dual union all select 20,'AAA','PRIMARY' from dual union all select 8 ,'AAA','SECONDARY' from dual union all select 7 ,'AAA','SECONDARY' from dual union all select 7 ,'AAA','PRIMARY' from dual union all select 8 ,'AAA','SECONDARY' from dual union all select 9 ,'AAA','SECONDARY' from dual ) select distinct COLUMNA, COLUMNB, COLUMNC from ( select COLUMNA, COLUMNB, COLUMNC, count(DISTINCT COLUMNC) over (partition by COLUMNA) as x, count(COLUMNC) over (partition by COLUMNA) as y from your_data ) yd where -- if columnc candidate rows includes only PRIMARY take PRIMARY (COLUMNC = 'PRIMARY' and x = 1) or -- if columnc candidate rows includes only SECONDARY take SECONDARY (just one record) (COLUMNC = 'SECONDARY' and x = 1 and y=1) or -- if columnc candidate rows includes PRIMARY and SECONDARY take PRIMARY (COLUMNC = 'PRIMARY' and x = 2) order by 1
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

if columnc candidate rows includes duplicate SECONDARY set skip record. Again 8 shouldnt be included, because COLUMNA 8 candidates always have SECONDARY in COLUMNC
And why 9 yes then?
because 9 is single record. SECONDARY included if have single record. But it duplicates or triplicates, it has to be skipped.
I have modified the query, does it meet your goal now? Maybe you can simplify it, but I keep it this way to show each filter related to your description
Matured by some special reason? Do you understand it?
|
0

It looks like aggregation does what you want:

SELECT COLUMNA, COLUMNB, MIN(COLUMNC) FROM t GROUP BY COLUMNA, COLUMNB HAVING MIN(COLUMNC) = 'PRIMARY';

Actually, based on your logic (but not the results which are inconsistent), WHERE is sufficient:

SELECT COLUMNA, COLUMNB, MIN(COLUMNC) FROM t WHERE COLUMNC = 'PRIMARY' GROUP BY COLUMNA, COLUMNB;

Or for the entire row:

SELECT t.* FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY Column1) as seqnum FROM t WHERE COLUMNC = 'PRIMARY' ) t WHERE seqnum = 1;
Improve this answer

2 Comments

@ÖmrümÇetin . . . That just requires a HAVING clause.
imagine this row has 100 columns so I need to fetch row by itself. MAX MIN wont work here. I need entire row. Now 3 columns... I can make min to all 98 columns.
0

CInspired by @Jaime Drq have solution below. Easy to understand and optimized query.

WITH TABLE_VIEW AS ( SELECT /*+ ORDERED */ COLUMNA, COLUMNB, COLUMNC, ROW_NUMBER() OVER (PARTITION BY COLUMNA, COLUMNB ORDER BY COLUMNC ASC) CANDIDATERANKING FROM TABLE) SELECT * FROM ( SELECT COLUMNA, COLUMNB, COLUMNC, SUM(CANDIDATERANKING) PRIORITY FROM TABLE_VIEW TT GROUP BY COLUMNA, COLUMNB, COLUMNC) WHERE PRIORITY= 1;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.