Re: "ORDER BY" question

"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.
>

or by prefixing 1 digit values with '0' ('6 MINS' becomes '06 MINS' and will sort ok). Not as beautiful as Charles' solution though...

Robbert Received on Thu Jul 20 2006 - 04:13:55 CDT