RE: numeric sorting on a char field having a decimal

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