RE: numeric sorting on a char field having a decimal
Jaini needed to sort a character column of version-like values (7.4, 7.5, 7.11) numerically after the decimal point, and TRUNC attempts failed. Rob Konikoff suggested splitting the column into integer and fractional parts, sorting, and rejoining. Scottishpoet advised redesigning to store the parts in separate columns and order by both. Jaini asked for the split query; no query was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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