"ORDER BY" question

Mr.Kane · *nix forums beginner Joined: 19 Jul 2006 Posts: 2

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

Charles Hooper · *nix forums addict Joined: 09 Jul 2006 Posts: 51

Mr.Kane wrote:

Robbert van der Hoorn · *nix forums beginner Joined: 29 Jun 2006 Posts: 16

"Charles Hooper" <hooperc2000@yahoo.com> wrote in message
news:1153350895.019503.45800@p79g2000cwp.googlegroups.com...

Mr.Kane · *nix forums beginner Joined: 19 Jul 2006 Posts: 2

Thank you both for your time on my issue.

-M

Robbert van der Hoorn wrote:

Google