Hello Tom,
I am working on a current project wherein the requirement to calculate a certain column in a certain table is as under.
The base table is this:
create table main_data
(from_value varchar2(10),
to_value varchar2(10),
activity_date date
);
insert into main_data
values('AAA','BBB',sysdate);
insert into main_data
values('BBB','AAA',sysdate-1);
insert into main_data
values('CCC','AAA',sysdate-3);
insert into main_data
values('AAA','CCC',sysdate-2);
commit;
The Data looks like this:
SQL> select * from main_data;
FROM_VALUE TO_VALUE ACTIVITY_DATE
---------- ---------- ---------
AAA BBB 28-NOV-14
BBB AAA 27-NOV-14
CCC AAA 25-NOV-14
AAA CCC 26-NOV-14
Now the requirement is: For a combination of AAA, BBB or BBB,AAA I need to calculate the MIN(ACTIVITY_DATE).
I.e., for the above scenario, I should get an output like this:
FROM_VALUE TO_VALUE MIN(ACTIVITY_DATE)
---------- ---------- ---------
AAA BBB 27-NOV-14
AAA CCC 25-NOV-14
How can I do this effectively using SQL or PL-SQL?
Thanks,
Suddhasatwa
ops$tkyte%ORA11GR2> select least( from_value, to_value ), greatest( from_value, to_value ), min(activity_date)
2 from main_data
3 group by least( from_value, to_value ), greatest( from_value, to_value )
4 order by 1, 2
5 /
LEAST(FROM GREATEST(F MIN(ACTIV
---------- ---------- ---------
AAA BBB 27-NOV-14
AAA CCC 25-NOV-14