SEO

Ok gang, I have to put up with this stuff all the time from our
customer. Weird number schemes... The only way I figured out how to do
it is SPLIT the column so that the mantissa is in a text column, then
sort it as text where 4 will come before 40, and then put the two
columns back together.

I usually do it in UNIX rather than INFORMIX, but that's because we
don't have big tables to do this with, and we only need to do it to set
a job up, not as part of the actual data analysis.

If you're doing LARGE volumes of numbers like this, then you may not
want to do it in UNIX.

Rob

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Jaini
Sent: Tuesday, January 31, 2006 10:30 AM
To: informix-list@iiug.org
Subject: Re: numeric sorting on a char field having a decimal

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.

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

That means that in one SELECT statement this is not possible. I don't
have a huge data to process but this table is access is frequently.

Anyways, can you help me in getting the query for splitting, sorting
and joining as I'm new to database.

Thanks

if its something you need to do frequently then a little bit of
redisgn! Store the bit before the "decimal" seperator and the bit after
the ."decimal" seperator as seperate columns

then

select before, after, before||"."||after
order by 1, 2

if its something you need to do frequently then a little bit of
redisgn! Store the bit before the "decimal" seperator and the bit after
the ."decimal" seperator as seperate columns

then

select before, after, before||"."||after
order by 1, 2