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

I thought I would give this problem a shot. You need to seperate both parts of the number, ie before and after the period.

  1 select mycol from junk order by
  2 to_number( substr(mycol,1,decode(instr(mycol,'.'),0,length( mycol ) + 1,instr(mycol,'.') ) -1)),
  3 to_number( substr( mycol,instr(mycol,'.') +1))   4*

You have to find where the period is ( instr( mycol,'.' ). The decode in line 2 is required to handle strings that do not have periods, such as '35'.
If there are multiple sections in the string such as 35.14.621, you will need to look for the 1st or second period using the instr command and add a third order by section.

Sorry it took so long to answer this. I haven't read my mail for a couple of days and was upto 366 mail messages today.

Robert Bunting
Missouri Employer's Mutual
Columbia, Mo
314-499-4135

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.
>
Received on Tue Jan 23 1996 - 20:29:02 CST