Search code examples
oracle-databaseduplicatesdefault-value

SQL: trying to choose one options as an default when it has duplicated (oracle)


I have some problem in SQL

My data is like this

Company Report date source
01 20071231 01
01 20081231 01
01 20081231 02
02 20071231 02
02 20081231 02
03 20071231 01
03 20071231 02

I want to choose '01' like an default in source column. But if not, then I want to choose source '02'

The result what I want is like this

Company Report date source
01 20071231 01
01 20081231 01
02 20071231 02
02 20081231 02
03 20071231 01

Thanks

select company
     , report date
     , source
from   (
        select *,
         count(*) over
           (partition by company, report date, source) as b
from the table
where b > 1

I cannot figure it out this result is true or not


Solution

  • Perhaps sorting rows per (company and date) by (source), so that 01 (if it exists) comes before 02 or any other value, and then extract rows that ranked as the highest?

    Sample data:

    SQL> with test (company, report_date, source) as
      2    (select '01', 20071231, '01' from dual union all
      3     select '01', 20081231, '01' from dual union all
      4     select '01', 20081231, '02' from dual union all
      5     select '02', 20071231, '02' from dual union all
      6     select '02', 20081231, '02' from dual union all
      7     select '03', 20071231, '01' from dual union all
      8     select '03', 20071231, '02' from dual
      9    ),
    

    Query begins here:

     10  temp as
     11    (select company, report_date, source,
     12       row_number() over (partition by company, report_date order by source) rn
     13     from test
     14    )
     15  select company, report_date, source
     16  from temp
     17  where rn = 1;
    
    COMPANY    REPORT_DATE SOURCE
    ---------- ----------- ----------
    01            20071231 01
    01            20081231 01
    02            20071231 02
    02            20081231 02
    03            20071231 01
    
    SQL>