| Getting the Minimum of a date column [message #628780] |
Fri, 28 November 2014 07:03  |
 |
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
Hello,
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
[Updated on: Fri, 28 November 2014 07:12] Report message to a moderator |
|
|
|
| Re: Getting the Minimum of a date column [message #628782 is a reply to message #628780] |
Fri, 28 November 2014 07:15  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
SQL> SELECT from_value, to_value, MIN(activity_date)
2 FROM (SELECT least(from_value, to_value) from_value,
3 greatest(from_value, to_value) to_value,
4 activity_date
5 FROM main_data
6 )
7 GROUP BY from_value, to_value
8 /
FROM_VALUE TO_VALUE MIN(ACTIVITY_DATE)
---------- ---------- ------------------
AAA CCC 25-Nov-2014 14:05:
AAA BBB 27-Nov-2014 14:05:
SQL>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
