Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: "ORDER BY" question
Thank you both for your time on my issue.
-M
Robbert van der Hoorn wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> news:1153350895.019503.45800_at_p79g2000cwp.googlegroups.com...
> > Mr.Kane wrote:
> > > we are evalutating performance and so this is a query that put
> > > together;
> > >
> > > SELECT prcsname , prcstype , runcntlid, begindttm, enddttm ,
> > > floor(((enddttm-begindttm)*24*60*60)/3600) || ' HOURS ' ||
> > > floor((((enddttm-begindttm)*24*60*60) -
> > > floor(((enddttm-begindttm)*24*60*60)/3600)*3600)/60) || ' MINUTES ' ||
> > > round((((enddttm-begindttm)*24*60*60) -
> > > floor(((enddttm-begindttm)*24*60*60)/3600)*3600 -
> > > (floor((((enddttm-begindttm)*24*60*60) -
> > > floor(((enddttm-begindttm)*24*60*60)/3600)*3600)/60)*60))) || ' SECS '
> > > time_difference
> > >
> > > FROM PSFT.psprcsrqst
> > >
> > > WHERE begindttm > TO_DATE ('07-18-2006','MM-DD-YYYY')
> > > ORDER BY time_difference DESC;
> > >
> > > ( I have tried to make it as intelligible as possible)
> > >
> > > Anyway. the result set from running this looks something like this
> > >
> > > PRCSNAME I PRCSTYPE I RUNCNTLID I BEGINDTTM I ENDDTTM I TIME_DIFFERENCE
> > >
> > > The "TIME_DIFFERENCE" field has the following format
> > > 00 HOURS 00 MINUTES 00 SECS
> > >
> > > unfortunately the "ORDER BY" sorts the result set in the following
> > > curious order:
> > >
> > > 0 HOURS 6 MINS 13 SECS
> > > 0 HOURS 59 MINS 2 SECS
> > > 0 HOURS 56 MINS 34 SECS
> > > 0 HOURS 48 MINS 58 SECS
> > > 0 HOURS 4 MINS 24 SECS
> > > 0 HOURS 34 MINS 42 SECS
> > > 0 HOURS 31 MINS 20 SECS
> > > etc
> > >
> > > how can I adjust the ORDER BY clause to take this format into account,
> > > so that it will sort in DESC order properly?
> > >
> > > Thank you
> > > Marc Kane
> > > Intermediate SQL Programmer
> > > and aspiring OCA 9i/10g professional
> >
> > ORDER BY
> > (enddttm-begindttm) DESC
> >
> > Sorting the records based on a numeric result comparision rather than
> > an ASCII text result comparision. '20000' < '3' but 20000 > 3
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
> >
>