CODEWITHSUNDEEP
sqloracle-database
Blackbird
Blackbird

Reputation: 23

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 to 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!

Upvotes: 0

Views: 83

Answers (2)

David Aldridge
David Aldridge

Reputation: 52376

I would think that this could be solved fairly simply by checking whether the highest and lowest values are the same, with:

select
  t.*,
  case
    when Greatest(number1, number2, number3) =
            Least(number1, number2, number3)
    then 1
    else 0
    end as no_duplicates
from t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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;

Upvotes: 1

Related Questions