"ORDER BY" question

From: Mr.Kane <kane.marc_at_gmail.com>
Date: 19 Jul 2006 15:53:24 -0700
Message-ID: <1153349604.696946.131740@m73g2000cwd.googlegroups.com>

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 Received on Wed Jul 19 2006 - 17:53:24 CDT