Search code examples
sqlsqlitehaving

SQL - how to find unique values from 2 columns?


I'm using SQLite, and would like to get only rows with values that are unique per table. I know how to get values per each column, but can not create a single (sqlite compatible) select to get unique value from both column.

Here is data example:

col_A col_B
111 777
222 333
222 111
444 333
222 555
333 666
333 777

My desired result from above example would be rows with unique values that include only: 444, 555, 666.

col_A col_B
444 333
222 555
333 666

222 should not be in results as it appears multiple times in col_A. 777 should not be in results as it appears multiple times in col_B. I need only single occurrence of value in either col_A or col_B. Unique value per table, not column.

How far i managed to get:

SELECT * 
    FROM my_table
    WHERE 
        (SELECT col_A as asset from my_table GROUP BY col_A HAVING COUNT(*) = 1
         UNION
        SELECT col_B as asset from my_table GROUP BY col_B HAVING COUNT(*) = 1)
            IN (col_A, col_B)

I have tried with UNION and few other approaches, but can not achieve desired result.


Solution

  • WITH
      symetric AS
    (
      SELECT col_a, col_b, col_a AS lookup FROM t
      UNION ALL
      SELECT col_a, col_b, col_b AS lookup FROM t
    )
    SELECT
      col_a, col_b
    FROM
      symetric   AS t
    WHERE
      NOT EXISTS (
        SELECT * 
          FROM symetric
         WHERE lookup          = t.lookup
           AND (col_a, col_b) <> (t.col_a, t.col_b)
      )
    ORDER BY
      col_a, col_b
    

    Demo ; https://dbfiddle.uk/9tzUASle