How do I select non null unequal values across columns?

0
votes

I would like to select records where values across 5 columns, if not null, are unequal. Any number of columns can be non null.

schema: given table

**Schema (SQLite v3.30)**

    CREATE TABLE test (
      id INT,
      col1 text,
      col2 text,
      col3 text,
      col4 text,
      col5 text
    );
    INSERT INTO test (id, col1, col2, col3, col4, col5) VALUES (1, 'EA', 'EA', null, null, null);
    INSERT INTO test (id, col1, col2, col3, col4, col5) VALUES (2, 'EA', 'MT', null, null, null);
    INSERT INTO test (id, col1, col2, col3, col4, col5) VALUES (3, null, 'EA', null, 'KG', null);
    INSERT INTO test (id, col1, col2, col3, col4, col5) VALUES (4, null, null, 'KG', 'MT', 'AB');
    INSERT INTO test (id, col1, col2, col3, col4, col5) VALUES (5, null, null, 'EA', 'MT', 'EA');

---

**Query #1**

    SELECT * FROM test;

| id  | col1 | col2 | col3 | col4 | col5 |
| --- | ---- | ---- | ---- | ---- | ---- |
| 1   | EA   | EA   |      |      |      |
| 2   | EA   | MT   |      |      |      |
| 3   |      | EA   |      | KG   |      |
| 4   |      |      | KG   | MT   | AB   |
| 5   |      |      | EA   | MT   | EA   |

---

Desired output

| id  | col1 | col2 | col3 | col4 | col5 |
| --- | ---- | ---- | ---- | ---- | ---- |
| 2   | EA   | MT   |      |      |      |
| 3   |      | EA   |      | KG   |      |
| 4   |      |      | KG   | MT   | AB   |
| 5   |      |      | EA   | MT   | EA   |

EDIT: To clarify, id=5 is included in the output since not all values are the same. When I stated values across all columns are unequal, I meant if if any value is distinct from the rest of the non null values. To state it differently, I require records which have at least 1 non null value different from the other non null value.

2
sqlsqlite
Do you want no duplicates or all values unequal? - Gordon Linoff
Can you please elaborate? I meant to say if 2 or more values are non null they should be unequal. That's what I'm querying for. - scientific_explorer

2 Answers

2
votes

Hmmm . . . this is tricky. Assuming that the empty values are NULL and no values are blank and you want all distinct values, you can do:

select t.*
from test t
where (col1 is null or col1 not in (coalesce(col2, ''), coalesce(col3, ''), coalesce(col4, ''), coalesce(col5, ''))) and
      (col2 is null or col2 not in (coalesce(col3, ''), coalesce(col4, ''), coalesce(col5, ''))) and
      (col3 is null or col3 not in (coalesce(col4, ''), coalesce(col5, ''))) and
      (col4 is null or col4 not in (coalesce(col5, '')))

EDIT:

If you just want more than one distinct value, then:

select t.*
from test t
where col1 not in (coalesce(col2, ''), coalesce(col3, ''), coalesce(col4, ''), coalesce(col5, '')) or
      col2 not in (coalesce(col1, ''), coalesce(col3, ''), coalesce(col4, ''), coalesce(col5, '')) or
      col3 not in (coalesce(col1, ''), coalesce(col2, ''), coalesce(col4, ''), coalesce(col5, '')) or
      col4 not in (coalesce(col1, ''), coalesce(col2, ''), coalesce(col3, ''), coalesce(col5, ''))

Here is a db<>fiddle.

Note: This does not return rows with only one value which technically do not meet your specifications.

1
votes

You can aggregate on the union of each id with 1 of the columns and set the condition in the having clause:

with cte as (
  select id from (
    select id, col1 col from test union all
    select id, col2 from test union all
    select id, col3 from test union all
    select id, col4 from test union all
    select id, col5 from test
  )
  where col is not null
  group by id
  having count(distinct col) > 1
)
select * from test where id in cte

See the demo.
Results:

> id | col1 | col2 | col3 | col4 | col5
> -: | :--- | :--- | :--- | :--- | :---
>  2 | EA   | MT   | null | null | null
>  3 | null | EA   | null | KG   | null
>  4 | null | null | KG   | MT   | AB  
>  5 | null | null | EA   | MT   | EA