Re: [Help] How to sort like this with SQL?

From: Scott Mattes <smattes_at_INTERSERF.NET>
Date: Mon, 22 Jan 1996 06:02:51 +0000
Message-Id: <9601221124.AA15205@alice.jcc.com>

> Date: Mon, 22 Jan 1996 08:19:49 +0200
> Reply-to: "ORACLE database mailing list." <ORACLE-L_at_CCVM.SUNYSB.EDU>
> From: Toni Gustafsson <tg_at_ENATOR.FI>
> Subject: Re: [Help] How to sort like this with SQL?
> X-To: oracle-l_at_CCVM.SUNYSB.EDU
> To: Multiple recipients of list ORACLE-L <ORACLE-L@CCVM.SUNYSB.EDU>

The problem with this solution is that it changes the VALUE of the data (1.05 is not the same as 1.5!)

I don't understand why you would want the value that falls between 1.1 and 1.2 (1.11) to be sorted after 1.5. Short of a multi field order by with the first field being something like 'length(field)' and the second being the column with the data I am not sure.
> Hi,
>
> I have used the following SQL to convert all numeric fields so that they are
> two characters logn, after wich I believe You would get the right results
> (assuming that each field has max 2 numbers).
>
> select Work_Item
> from Work_Table
> order by
> substr (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> replace (
> '.' || replace (Work_Item,'.','..'),
> '.0.','.00.'),
> '.1.', '.01.'),
> '.2.', '.02.'),
> '.3.', '.03.'),
> '.4.', '.04.'),
> '.5.', '.05.'),
> '.6.', '.06.'),
> '.7.', '.07.'),
> '.8.', '.08.'),
> '.9.', '.09.'),
> '..', '.'),
> 2,254);
>
> I hope this helps
>
> Greetings,
>
> Toni
>
>

---

Scott Mattes
I-Net, Inc.

We are free to choose what we will do, think, say, be, but we are not free to choose the consequences of those choices. Received on Mon Jan 22 1996 - 06:24:41 CST