Re: numeric sorting on a char field having a decimal
Jaini wrote:
> This didn't help me..
>
> col TRUNC(col+0), (col+0 -
> TRUNC(col+0))
> 7.11 7 0.11000000000000
> 7.23 7 0.23000000000000
> 7.24 7 0.24000000000000
> 7.29 7 0.29000000000000
> 7.34 7 0.34000000000000
> 7.40 7 0.40000000000000
> 7.4 7 0.40000000000000
> 7.45 7 0.45000000000000
> 7.5 7 0.50000000000000
>
> you can see 4 & 5 are coming still below in the list.
>
> But the requirement I have is 7.4, 7.5, 7.11, 7.23, .......
>
> Kindly suggest.
>
OK Modify the Clown's suggestion to:
SELECT col, TRUNC(col+0), (col+0 - TRUNC(col+0)) * 100
FROM tab
ORDER BY 2, 3;
Art S. Kagel
Art S. Kagel said:
>
> Jaini wrote:
>> This didn't help me..
>>
>> col TRUNC(col+0), (col+0 -
>> TRUNC(col+0))
>> 7.11 7 0.11000000000000
>> 7.23 7 0.23000000000000
>> 7.24 7 0.24000000000000
>> 7.29 7 0.29000000000000
>> 7.34 7 0.34000000000000
>> 7.40 7 0.40000000000000
>> 7.4 7 0.40000000000000
>> 7.45 7 0.45000000000000
>> 7.5 7 0.50000000000000
>>
>> you can see 4 & 5 are coming still below in the list.
>>
>> But the requirement I have is 7.4, 7.5, 7.11, 7.23, .......
>>
>> Kindly suggest.
>>
> OK Modify the Clown's suggestion to:
>
> SELECT col, TRUNC(col+0), (col+0 - TRUNC(col+0)) * 100
> FROM tab
> ORDER BY 2, 3;
Unless, of course, he goes down to three digits after the decimal. ;o)
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
- Coluche
did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.
--r937, dbforums.com
Art S. Kagel Said >>OK Modify the Clown's suggestion to: Ouch.