numeric sorting on a char field having a decimal
A poster had a CHAR(6) column holding values like "10.3" and "10.22" and wanted them sorted "numerically" in an unusual order. Suggestions to cast to DECIMAL or add 0 (col::DECIMAL, col+0) failed because 10.4 and 10.40 then compare equal and the desired ordering isn't true numeric order. Clarified that he wanted the part after the decimal point treated as a separate integer, so 10.3 precedes 10.22. Workarounds offered: ORDER BY c+0, length(c) (9.4 allows ordering by non-selected expressions), and Marco Greco's stored procedure that extracts the digits after the dot as an INT and sorts on that. The requested ordering was still inconsistent, and the poster never confirmed a fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, In Informix, I have a char(6) column in which decimals are stored. I need to sort in the numeric order. I have seen couple of query posted before which deals with numeric stored in a char field but I'm looking for decimals stored in the char. I need the o/p in following format - 10.1 10.2 10.3 10.10 10.22 10.29 10.4 Thanks & Regards Abhinav
Jaini said: > > Hi, > > In Informix, I have a char(6) column in which decimals are stored. I > need to sort in the numeric order. I have seen couple of query posted > before which deals with numeric stored in a char field but I'm looking > for decimals stored in the char. > > I need the o/p in following format - > > 10.1 > 10.2 > 10.3 > 10.10 Actually, 10.10 = 10.1 > 10.22 > 10.29 > 10.4 If you have IDS 9, you can cast them to DECIMAL. If not, you can try adding 0.00 to each. SELECT col::DECIMAL FROM tab ORDER BY 1 or SELECT col + 0.00 FROM tab ORDER BY 1 Neither option has been tested. :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
Jaini wrote: > Hi, > > In Informix, I have a char(6) column in which decimals are stored. I > need to sort in the numeric order. I have seen couple of query posted > before which deals with numeric stored in a char field but I'm looking > for decimals stored in the char. > > I need the o/p in following format - > > 10.1 > 10.2 > 10.3 > 10.10 > 10.22 > 10.29 > 10.4 > You don't say which version you have. For 7.x, you SELECT char_column + 0 ... ORDER BY 1 For 9.x, you do as for 7.x or SELECT char_column::DECIMAL ... ORDER BY 1 You'd be better off fixing the table though. -- rh
if you want these sorted in that order then these are not decimals so you are unlikely to 10.22 is a smaller number than 10.3 and bugg er ten point twenty two is not a decimal number and may well be bigger than ten point three
Obnoxio, It didn't help me as, the in the result the deciamls are padded with ZERO's and there is no difference between 10.4 & 10.40. I want the result in following format - 10.1 10.4 10.11 10.40 10.45 But casting or adding 0.00 is resulting (expression) 10.10000000 10.11000000 10.40000000 10.40000000 10.45000000 Kindly suggest other way. Thanks for your time.
Jaini said: > > Obnoxio, > > It didn't help me as, the in the result the deciamls are padded with > ZERO's and there is no difference between 10.4 & 10.40. I want the > result in following format - > > 10.1 > 10.4 > 10.11 > 10.40 > 10.45 > > But casting or adding 0.00 is resulting > > (expression) > 10.10000000 > 10.11000000 > 10.40000000 > 10.40000000 > 10.45000000 Well, yes, in a decimal number, 10.4 = 10.40. So what you are saying is that while it contains a decimal point, the number is not, in fact, a decimal at all. So what are you actually trying to do? -- 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
Jaini wrote:
> Hi,
>
> In Informix, I have a char(6) column in which decimals are stored. I
> need to sort in the numeric order. I have seen couple of query posted
> before which deals with numeric stored in a char field but I'm looking
> for decimals stored in the char.
>
> I need the o/p in following format -
>
> 10.1
> 10.2
> 10.3
> 10.10
> 10.22
> 10.29
> 10.4
>
> Thanks & Regards
> Abhinav
you do realize that the order that you want is neither in char order nor
numeric order, right? in neither 10.10 would be bigger than 10.3
in 9.4 you can order by expressions not in the select list, eg
select c from t order by c+0, length(c)
yields
10.1
10.10
10.11
10.2
10.22
10.29
10.3
10.4
just write an SP that sorts stuff by whatever warped order you like
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
10.four is smaller than 10.forty in this alternative decimal notation
Obnoxio, I agree with you. This is what I'm looking for - I need to sort the numbers considering only after decimals so that 3 comes before 22 in 10.3 & 10.22. Thanks
Jaini said:
>
> Obnoxio,
>
> I agree with you. This is what I'm looking for -
>
> I need to sort the numbers considering only after decimals so that 3
> comes before 22 in 10.3 & 10.22.
Pffft.
Something like
SELECT col, TRUNC(col+0), (col+0 - TRUNC(col+0))
FROM tab
ORDER BY 2, 3
?
--
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
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.
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.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
sigh! try the following
create procedure "informix".warpedsort(c char(6)) returning int;
define i,l,s int;
let s=0;
let l=0;
for i in (1 to 6)
if (substring(c from i for 1)=".")
then
let s=i+1;
let l=6-i+1;
end if
end for
if (s>0 and l>0)
then
return substring(c from s for l)::int;
else
return 0;
end if;
end procedure;
select c from t order by warpedsort(c)
which yields
10.1
10.2
10.3
10.4
10.10
10.11
10.22
10.29
still doesn't explain 22 (as in 10.22) would be bigger than 3 (as in 10.3) but
smaller than 4 (as in 10.4)
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm