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.
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