Blackbird Blackbird - 5 days ago 7
SQL Question

Oracle PL/SQL: Distinct Columns instead of rows

I need to work with a table that looks like this:


Date | Number1 | Number2 | Number 3
---------------------------------------------------------
29.11.2016 | 7 | 7 | 5
---------------------------------------------------------
30.11.2016 | 5 | 6 | 7


And I need wo define a function to go through the table and return a boolean. TRUE, if there are no duplicate values in Number1, Number2, Number3 and FALSE if there are duplicate values. The Problem is, that there could also be a Number 4 or a number 5, there can be unlimited columns and the function should be able to deal with that.

What would be an efficient way to do so? Thanks in advance!

Answer

You have a poor data structure. This would be much easier with values in columns, so let's unpivot the data and do the query that way. The following assumes that date is distinct:

with t as (
      select date, number1 as num from t union all
      select date, number2 from t union all
      select date, number3 from t
     )
select date,
       (case when count(*) = count(distinct num) then 'true' else 'false' end) as flag
from t
where num is null
group by date;

As a single select, this gets complicated:

select t.*,
       (case when number1 in (number2, number3) then 'false'
             when number2 in (number3) then 'false'
             else 'true'
        end) as flag
from t;

Okay, that isn't so bad. This is easily extended to multiple columns:

select t.*,
       (case when number1 in (number2, number3, number4, number5) then 'false'
             when number2 in (number3, number4, number5) then 'false'
             when number3 in (number4, number5) then 'false'
             when number4 in (number5) then 'false'
             else 'true'
        end) as flag
from t;
Comments