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

> On Mon, 22 Jan 1996, Taesoo,Cho wrote:
>
> Good Luck to all the people who read this!
>
> I have the table with one column and it has
> some datas like this:
>
> 1
> 1.1
> 1.10
> 1.2
> 1.5
> 1.11
> 1.3
> 1.4
> 1.12
> ...
>
> If I select this with later statement you can
> get this result:
>
> SELECT Work_Item
> FROM Work_Table
> ORDER BY Work_Item;
>
> 1
> 1.1
> 1.10
> 1.11
> 1.12
> 1.2
> 1.3
> 1.4
> 1.5
> ...
>
> But I want to sort this datas like this:
>
> 1
> 1.1
> 1.2
> 1.3
> 1.4
> 1.5
> 1.10
> 1.11
> 1.12
> ...
>
> How can I get this result? Please be help!
>
> I'm using Oracle 7.0 with SQL*Plus 3.1.
>
> Thanks in advance.
>
>

Assuming (yes I know, don't ass-u-me) the format is only 'number period numbers' try

  SELECT Work_Item, length(to_char(work_item)) sort_length   FROM Work_Table
  ORDER BY sort_length, Work_Item;

Andrew Hanson
Ogden Professional Services
U.S. EPA, NHEERL WEB
200 SW 35th Street
Corvallis, OR 97333
Phone: 541-754-4356 FAX: 541-754-4338 E-Mail: hansona_at_mail.cor.epa.gov

Opinions expressed are mine and do not reflect those of Ogden Professional Services or the EPA. Received on Thu Jan 25 1996 - 14:53:11 CST