Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: "ORDER BY" question

Re: "ORDER BY" question

From: Mr.Kane <kane.marc_at_gmail.com>
Date: 20 Jul 2006 13:56:51 -0700
Message-ID: <1153429011.389880.145100@b28g2000cwb.googlegroups.com>


Thank you both for your time on my issue.

-M

Robbert van der Hoorn wrote:
> "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 - 15:56:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US