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

Mr.Kane wrote:
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.

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> wrote

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

Thank you both for your time on my issue.


-M


Robbert van der Hoorn wrote: