We have a table like this:

Column A	Column B		Column C
Cell 1	201453		1000
Cell 2	201232		1000
Cell 3	213231		2000
Cell 2	201233		3000
Cell 1	200032		1000

Culumn A - may be repeated Column B - unique Column C - may be repeated

How do I find value (Column A), which have same valeu (Column C)????

I dont get it...

CodePudding user response：

If I'm getting the request right, you need to know each value os columnA that has the same value in ColumnC:

select LISTAGG("Column A",',') WITHIN GROUP (ORDER BY "Column A") from table1
group by "Column C"

test

http://sqlfiddle.com/#!4/303780/5

CodePudding user response：

Apparently, you need to count the duplicates for values of col_a vs. col_c pair . So, using a HAVING clause along with grouping by those two columns will do the trick such as

SELECT col_a
  FROM t
 GROUP BY col_a,col_c 
HAVING COUNT(*) > 1

Demo

CodePudding user response：

If you are looking for rows having the same (repeated) combination of COL_A and COL_C then try this:

WITH
    tbl AS
        (
            Select 1 "COL_A", 201453 "COL_B", 1000 "COL_C" From Dual UNION ALL
            Select 2 "COL_A", 201232 "COL_B", 1000 "COL_C" From Dual UNION ALL
            Select 3 "COL_A", 213231 "COL_B", 2000 "COL_C" From Dual UNION ALL
            Select 2 "COL_A", 201233 "COL_B", 3000 "COL_C" From Dual UNION ALL
            Select 1 "COL_A", 200032 "COL_B", 1000 "COL_C" From Dual            
        )

SELECT
    COL_A, COL_B, COL_C
FROM
    ( Select  COL_A, COL_B, COL_C, 
              Count(COL_B) OVER(Partition By COL_A, COL_C) "COUNT_A_C" 
      From    tbl )
WHERE   COUNT_A_C > 1

R e s u l t :
     COL_A      COL_B      COL_C
---------- ---------- ----------
         1     201453       1000 
         1     200032       1000 

Used analytic function Count() Over() - more about analytic functions here.

... Or with aggregate function Count(), Group By and Having - the result is the same as above

SELECT    t.COL_A, t.COL_B, t.COL_C
FROM      tbl t
INNER JOIN 
    ( Select  COL_A, COL_C, Count(COL_B) "COUNT_A_C" 
      From    tbl
      Group By COL_A, COL_C
      Having Count(COL_B) > 1) c ON (c.COL_A = t.COL_A And c.COL_C = t.COL_C)