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