Re: Problem getting data to "Orderby" in the way I want...

Originally posted by John Guthrie
> Hi all,
>
>
>
> I have a column that contains data running from 1.1 to 1.16.
> This data is
> really 2 decimal numbers separated by a decimal point rather than
> a float.
>
> If I do a Orderby to_number () in a select on this column, the
> data will
> display in the following order:
>
>
>
> 1.1
>
> 1.10
>
> 1.11
>
> 1.12
>
> 1.13
>
> 1.14
>
> 1.15
>
> 1.16
>
> 1.2
>
> 1.3
>
> 1.4
>
> 1.5
>
> 1.6
>
> 1.7
>
> 1.8
>
> 1.9
>
>
>
> What I actually want is the following order:
>
>
>
> 1.1
>
> 1.2
>
> 1.3
>
> 1.4
>
> 1.5
>
> 1.6
>
> 1.7
>
> 1.8
>
> 1.9
>
> 1.10
>
> 1.11
>
> 1.12
>
> 1.13
>
> 1.14
>
> 1.15
>
> 1.16
>
>
>
> Any ideas how I can go about getting the data to order in this way?
>
>
>
> Appreciate the help,
>
>
>
> John Guthrie

How about:

ORDER BY SUBSTR(col,1,INSTR(col,'.')-1), SUBSTR(col,INSTR(col,'.')+1)

Tip for future: if it's really 2 separate numbers, use 2 separate columns!

--
Posted via http://dbforums.com