Re: sorting within a group

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote in message news:<dVwFa.1199120$S_4.1217838_at_rwcrnsc53>...
> Why not just use the to_char to get the month?
> to_char(mydate,'mmmm') or to_char(mydate,'mm')
> select to_char(mydate,'mmmm') from mytable where .. order by
> to_char(mydate,'mm') asc;
>
> Jim
>
>
> "Michael Sterling" <stermic_at_gw.co.jackson.mo.us> wrote in message
> news:f7359f44.0306101240.3504aaf2_at_posting.google.com...
> > ok, here's the situation. i created a formula for to get the month out
> > of this date field. the steps i took are as follows, i first took the
> > month from the date field:
> >
> > Month({occup_main.occupied_date})
> >
> > this gave me the month in a number form. i.e. 1.00 = january.....12.00
> > = december.
> >
> > then, since i wanted the name of the month to show on the report
> > instead of that number i did this:
> >
> > MonthName(Month({occup_main.occupied_date}))
> >
> > then i made a group from this formula. the problem is that my records
> > are now grouped in the wrong order. in other words, when ever i run my
> > report my data is grouped in the alphabetical order of the months. in
> > other words..
> >
> > September
> >
> > i want to order the months the way are gregorian calender has them
> > ordered, january, february, march, april......december. how do i do
> > this

Nice solution Jim. Mike, Oracle built-in date formatting capability can handle just about anything you want. Unless you are trying to write an application that will run on several different database managers you should to avoid creating your own routines to do what Oracle can do for you. You will end adding overhead, and find situations like your example above where simple solutions are obscured by the additional routines.

IMHO -- Mark D Powell -- Received on Wed Jun 11 2003 - 09:18:25 CDT