Re: "ORDER BY" question

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. Received on Wed Jul 19 2006 - 18:14:55 CDT