drop table samp;
create table samp as
select '9-1' act, 300 beg, 350 nd from dual union all
select '9-2' act, 350 beg, 310 nd from dual union all
select '9-3' act, 310 beg, 301 nd from dual union all
select '9-4' act, 301 beg, 340 nd from dual union all
select '9-5' act, 340 beg, 330 nd from dual;
select
(select max(substr(act,1,1)) from samp) yr,
(select beg from samp s1 where not exists (select act from samp s2 where
s1.act > s2.act)) bop,
(select nd from samp s1 where not exists (select act from samp s2 where
s1.act < s2.act)) eop
from dual;
Mike
On Thu, Jun 18, 2009 at 9:40 AM, Mouhammed Iyad <iya...@gmail.com> wrote:
>
> min() & max() will return the minimum & maximum value
>
> but I want the value for the first month & the value for the last
> month of the year I choose
>
> Regards,
> Iyad
>
> On 18/06/2009, ddf <orat...@msn.com> wrote:
> >
> >
> >
> > On Jun 18, 10:46 am, Mouhammed Iyad <iya...@gmail.com> wrote:
> >> your right dear,
> >>
> >> it's varchar2
> >> & I used substr(act_month, 1, 4)
> >>
> >> but my question is about the beginning_of_period, end_of_period
> >> I want when I choose a year (in my example substr(act_month, 1, 4) =
> >> '2009')
> >> I need to get the value of the first month (in my example '2009-01')
> >> for beginning_of_period
> >> and the value of the last month (in my example '2009-05') for
> >> end_of_period
> >>
> >> Best Regards,
> >> Iyad
> >>
> >> On 18/06/2009, ddf <orat...@msn.com> wrote:
> >>
> >>
> >>
> >>
> >>
> >> > On Jun 18, 10:06 am, Mouhammed Iyad <iya...@gmail.com> wrote:
> >> >> Dears,
> >>
> >> >> I have the following table
> >>
> >> >> act_Month Beginning_of_period end_of_period
> >> >> ------------------------------------------------------------------
> >> >> 2009-01 300 350
> >> >> 2009-02 350 310
> >> >> 2009-03 310 301
> >> >> 2009-04 301 340
> >> >> 2009-05 340 330
> >>
> >> >> what i need is
> >> >> select left(act_month,4) ,
> >> >> Beginning_of_period value for the minimum month,
> >> >> end_of_period value for the maximum month
> >> >> from my_table
> >>
> >> >> in our example will give me
> >> >> 2009 , 300 , 330
> >>
> >> >> Many thanks for your assistant,
> >> >> Best Regards,
> >> >> Iyad
> >>
> >> > You do not provide enough information to answer the question. How is
> >> > act_month defined, as a varchar2 or as a date? From your example it
> >> > appears to be a varchar2, and for that you'd use substr():
> >>
> >> > select substr(act_month, 1, 4), beginning_of_period, end_of_period
> >> > from my_table;
> >>
> >> > All of this information is available in the online documentation found
> >> > here:
> >>
> >> >http://tahiti.oracle.com
> >>
> >> > David Fitzjarrell- Hide quoted text -
> >>
> >> - Show quoted text -
> >
> > Then you use the min() and max() functions, again documented in the
> > online manuals, and group by:
> >
> > select substr(act_month, 1, 4), min(beginning_of_period), max
> > (end_of_period)
> > from my_table
> > group by substr(act_month, 1, 4);
> >
> > Again all of this is in the manuals, which you should read.
> >
> >
> > David Fitzjarrell
> >
> >
> >
> > >
> >
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
