CODEWITHSUNDEEP
sqloracle-database
sgp667
sgp667

Reputation: 1875

Oracle SQL group by multiple OR conditions

Given that I have following table table1

field1 field2
A      B
A      C
D      B
D      C
E      F
G      F

I am trying to get a query that partitions my data and ranks members within each partition. I am expecting that my query will look something like the one below but I am not sure how I can achieve the field1 OR field2 condition:

SELECT 
  field1, field2, ???? as partition, 
  RANK() OVER ( PARTITION BY field1 OR field2) as rank
FROM table1

field1 field2 partition rank
A      B      1         1
A      C      1         2
D      B      1         3
D      C      1         4
E      F      2         1
G      F      2         2

Upvotes: 2

Views: 334

Answers (1)

MT0
MT0

Reputation: 168361

It's really not a simple/nice solution but this will do it:

Oracle Setup:

CREATE TABLE table_name (
  field1 VARCHAR2(10),
  field2 VARCHAR2(10)
);

INSERT INTO table_name
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'A', 'C' FROM DUAL UNION ALL
SELECT 'D', 'B' FROM DUAL UNION ALL
SELECT 'D', 'C' FROM DUAL UNION ALL
SELECT 'E', 'F' FROM DUAL UNION ALL
SELECT 'G', 'F' FROM DUAL;

CREATE OR REPLACE TYPE StringList AS TABLE OF VARCHAR2(50);
/

Query:

SELECT field1,
       field2,
       "partition",
       ROW_NUMBER() OVER ( PARTITION BY "partition" ORDER BY field1, field2 )
         AS "rank"
FROM   (
  SELECT field1,
         f.COLUMN_VALUE AS field2,
         DENSE_RANK() OVER ( ORDER BY partition_string ) AS "partition"
  FROM   (
    SELECT field1,
           CAST( COLLECT( field2 ORDER BY field2 ) AS StringList ) AS field2s,
           LISTAGG( field2, ',' ) WITHIN GROUP ( ORDER BY field2 )
             AS partition_string
    FROM   table_name
    GROUP BY field1
  ) t,
  TABLE( field2s ) f
);

Output:

FIELD1 FIELD2 partition  rank
------ ------ ---------- ----
A      B               1    1 
A      C               1    2 
D      B               1    3 
D      C               1    4 
E      F               2    1 
G      F               2    2

Upvotes: 2

Related Questions