Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Suddhasatwa.

Asked: November 28, 2014 - 1:00 pm UTC

Last updated: October 07, 2018 - 2:10 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

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

and Tom said...


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


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good Document .. helpful

Ashray, October 06, 2018 - 9:41 am UTC

Hi All,
Its a Good Document .. was helpful for improving the knowledge

But unfortunately this MIN(Date_start) din't work for me
It was throwing error

Infact I had a condition which was working
TO_CHAR(ppos.date_start,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN')

but i need to pickup the MIN of Date_Start , so I tried both the below conditions , but it was giving me "missing right parenthesis" error

TO_CHAR(MIN(ppos.date_start),'DD-MON-YYYY')
TO_CHAR(MIN(ppos.date_start),'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN')

Please let me know some alternative to fetch the MIN value of this date column,
Thank you in advance

Regards
Ashray

Connor McDonald
October 07, 2018 - 2:10 am UTC

"It was throwing error"

tells us ... nothing.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library