Re: numeric sorting on a char field having a decimal
Question: how to sort a CHAR(6) column holding decimal-looking strings (e.g. 10.1, 10.10, 10.2) in "numeric" order where the part after the point is treated as a separate integer, so 10.1 and 10.10 are different. Several approaches were posted: Marco Greco's simple 'order by c+0, length(c)', bozon's mantissa/notMantissa SPL functions splitting the string at the decimal point and casting each half to int, Nog's stored procedure plus CASE/TRUNC query, and Double Echo's Perl zero-padding/hash-sort script (which was criticised because padding merges 10.5 with 10.50 and loses the required distinction). The SPL split-at-the-point approach is the working answer; the thread ends in banter rather than a formal conclusion.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 > After having seen this problem go unsolved, and after all the people attempting to solve it, I thought I'd give you my .02. Since I'm not good with SQL I have to do things outside of SQL. It's easier for me to get the data into an array or hash, then sort the hash rather than trying to get it right with SQL that I can't grasp. :-) If you don't have to do it all in SQL, you can you take the output and feed it into another program. I created a little Perl program that might help you, if not for this problem, maybe another one. You can apply this same concept in ESQL/C, but that involves linked-lists and it will not be as obvious in C code what it happening. The key is in using the printf function to format the data, and generating a unique-key to sort on. Hashes are essentially the same as a linked list, only you don't have to code in C. Have fun! #!/usr/bin/perl ################################################################################ =begin program: sortdigits.pl usage: sortdigits.pl file=my-data-file example: ./sortdigits.pl file=sample-data.txt =cut ################################################################################ use CGI ; $co = new CGI ; $file = $co->param("file") ; ## ## use this if you don't have CGI installed ## just comment-out the above CGI code and ## uncomment this line: ## ## $file = $ARGV[0] ; ## ## then use the program thusly: ## ## usage: ./sortdigits.pl my-data-file ## $rowid = 0 ; open ( FILE, "<$file" ) ; my %data_hash ; my $rowid ; ## ## first load up our hash called $data_hash with values ## while ( <FILE> ) { ++$rowid ; $padded_count = sprintf "%4.4d", $rowid ; $raw_val = $_ ; ## ## take off carriage-returns and line-feeds ## $raw_val =~ /\\r$/ ; $raw_val =~ /\\n$/ ; ## ## pad our values with leading zeros - the heart of the program ## $v = sprintf ( "%05.2lf", $raw_val ) ; $key = "${v}_${padded_count}" ; ## ## load our hash to be sorted ## $data_hash{$key} = "$v\\|$padded_count" ; ## print "hash loading -- $data_hash{$key}\\n" ; ## } close ( FILE ) ; print "\\n\\n the sorted list \\n\\n" ; ## ## generate the sorted list and print it out ## foreach $key ( sort(keys(%data_hash) ) ) { $value_string = $data_hash{$key} ; ## printf ( "%s\\n", $value_string ) ; my @col_arr = split /\\|/, $value_string ; my $c_value = sprintf ( "%8.2f", $col_arr[0] ) ; my $counter = $col_arr[1] ; printf ( "%s -- $counter \\n", $c_value ) ; } exit ; ## ## -- end of program ## Use this for your data file: 10.5 10.50 7.11 7.23 7.24 7.29 7.34 7.4 7.40 7.45 7.5 8.11 8.23 8.24 8.29 8.34 8.4 8.40 8.45 8.5 Run the program : chmod 755 sortdigits.pl ./sortdigits.pl file=sampledata.txt hash loading -- 10.50|0001 hash loading -- 10.50|0002 hash loading -- 07.11|0003 hash loading -- 07.23|0004 hash loading -- 07.24|0005 hash loading -- 07.29|0006 hash loading -- 07.34|0007 hash loading -- 07.40|0008 hash loading -- 07.40|0009 hash loading -- 07.45|0010 hash loading -- 07.50|0011 hash loading -- 08.11|0012 hash loading -- 08.23|0013 hash loading -- 08.24|0014 hash loading -- 08.29|0015 hash loading -- 08.34|0016 hash loading -- 08.40|0017 hash loading -- 08.40|0018 hash loading -- 08.45|0019 hash loading -- 08.50|0020 the sorted list 7.11 -- 0003 7.23 -- 0004 7.24 -- 0005 7.29 -- 0006 7.34 -- 0007 7.40 -- 0008 7.40 -- 0009 7.45 -- 0010 7.50 -- 0011 8.11 -- 0012 8.23 -- 0013 8.24 -- 0014 8.29 -- 0015 8.34 -- 0016 8.40 -- 0017 8.40 -- 0018 8.45 -- 0019 8.50 -- 0020 10.50 -- 0001 10.50 -- 0002
Double Echo said: > > 10.50 -- 0001 > 10.50 -- 0002 But what happened to his 10.5? -- 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
Obnoxio The Clown wrote: > Double Echo said: > >> 10.50 -- 0001 >> 10.50 -- 0002 > > > But what happened to his 10.5? > I padded them all with zeros. 10.5 or 10.50 what's the difference? They each are indexed with a "rowid" to identify them, and I guess you could print out the original value from the select. Maybe somebody else can make a better select that gives better data to format with this perl example? If you can't get better data, then the hash key could be decomposed again with a split statement, but I'm not in front of my Linux box at the moment, so you'd have to do it yourself: @the_key = split /_/, $key ; $orig_value = $the_key[0] ; $index = $the_key[1] ; print "$orig_value\\n" ;
Double Echo wrote: > Obnoxio The Clown wrote: > >>Double Echo said: >> >> >>> 10.50 -- 0001 >>> 10.50 -- 0002 >> >> >>But what happened to his 10.5? >> > > > I padded them all with zeros. 10.5 or 10.50 what's the difference? Right! it's two days that the guy shouts that x.10 |= x.1, yet Double Echo claims to be the only one to have understood the specs? > > They each are indexed with a "rowid" to identify them, and I guess you > could print out the original value from the select. Maybe somebody else > can make a better select that gives better data to format with this > perl example? > > If you can't get better data, then the hash key could be decomposed again > with a split statement, but I'm not in front of my Linux box at the moment, > so you'd have to do it yourself: > > @the_key = split /_/, $key ; > $orig_value = $the_key[0] ; > $index = $the_key[1] ; > > print "$orig_value\\n" ; > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- 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
Marco Greco wrote: > Double Echo wrote: >> I padded them all with zeros. 10.5 or 10.50 what's the difference? > > > Right! it's two days that the guy shouts that x.10 |= x.1, yet Double > Echo claims to be the only one to have understood the specs? > I didn't claim that, calm down, sheesh. I read through every one of the posts, and I even valued my answer at .02. I probably don't get it, but was willing to try. I'll try better next time I promise. :-) I even corrected my mistake, please don't shoot me. I am in awe of the people who can use SQL so masterfully, but even they had problems, so I offer an alternative, and I have to say, the SQL I see here is exceptional, including yours Marco, not what I typically see in the real world--or my world anyway. Bye!
Double Echo Said
>After having seen this problem go unsolved, and after all the people attempting to solve it, I thought I'd give you my .02.
I solved it on the first day with the mantissa, notmantissa procedures.
create function mantissa( s varchar(255) )
returning int with ( not variant) ; set debug file to "debug.out";
if (s like "%.%") then
trace "s=" || s;
while ( substr(s,1,1) <> '.' )
let s = substr(s,2) ;
end while
trace "s=" || s;
-- Skip period
let s = substr(s,2);
else
let s = "0" ;
end if
return s::int ;
end function ;
create function notMantissa( s varchar(255) )
returning int with ( not variant) ; define l int;
if (s like "%.%") then
let l=length(s);
while ( l <> 1 and substr(s,l,1) <> '.' )
let l = l-1;
let s = substr(s,1,l) ;
end while
-- Skip period
if ( l = 1 ) then
let s = "0";
else
let s = substr(s,1,l-1) ;
end if
else
let s = "0" ;
end if
return s::int;
end function ;
I then built onto Marco Greco's partial solution and solved it another
way.
>>Marco said:
>>select c from t order by c+0, length(c)
>Nice work Marco, but I think he wants the following
>select abbinormal, abbinormal::int, length(abbinormal),>abbinormal::decimal(10,5) - abbinormal::int from decimaul order by 2,3,4 ;
Maybe I didn't make it clear that both times I presented a solution it
was correct. I did present a select output with the data in the correct
order both times.
I of course like everyone else was trying to come up with a better
solution, more elegant and faster solution (if it was slower but more
elegant I would have preferred that also ;-).
create a procedure;
create procedure proc1(str CHAR(6))
RETURNING SMALLINT;
DEFINE pos SMALLINT;
DEFINE ret SMALLINT;
LET pos = 0;
LET ret = 0;
FOR pos = LENGTH(str) TO 1 STEP -1
IF substr(str,pos,1) != "."
THEN
LET ret = ret + 1;
ELSE
EXIT FOR;
END IF
END FOR
RETURN ret;
END PROCEDURE;
then use the following SQL;
SELECT
trunc(CASE
WHEN (proc1(dec_val) = 1) THEN
TRUNC(dec_val,0) || "." || LPAD(MOD(dec_val*10,10),2,0)
WHEN (proc1(dec_val) = 2) THEN
(TRUNC(dec_val,0) || "." || MOD(dec_val*100,100)+0)
END,2) AS VAL
from dodgy_table
order by 1;
for the given data;
10.5
10.50
7.11
7.23
7.24
7.29
7.34
7.4
7.40
7.45
7.5
8.11
8.23
8.24
8.29
8.34
8.4
8.40
8.45
8.5
you get ;
7.04
7.05
7.11
7.23
7.24
7.29
7.34
7.40
7.45
8.04
8.05
8.11
8.23
8.24
8.29
8.34
8.40
8.45
10.05
10.50
works with ids 7.3x
Double Echo said: > > Obnoxio The Clown wrote: >> Double Echo said: >> >>> 10.50 -- 0001 >>> 10.50 -- 0002 >> >> >> But what happened to his 10.5? >> > > I padded them all with zeros. 10.5 or 10.50 what's the difference? In this case, 10.5 < 10.50. Yeah, I know, it doesn't make any sense to me either. -- 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
bozon wrote: > Double Echo Said > >>After having seen this problem go unsolved, and after all the people attempting to solve it, I thought I'd give you my .02. > > > I solved it on the first day with the mantissa, notmantissa procedures. > Well, you and others are certainly better at SQL than me, I never was good at SQL. Not only am I bad at SQL, my math is lacking too! :-) I certainly was in last place for solutions here, please pass that along to the judges. Your solution as well as others ( ahem Marco! :-) was very good, and great to see such SQL mastery, as well as excellence in math. But I'm not one to trust SQL as much as some do, and would rather depend on manipulating the data outside of SQL. These functions may also not be available if I wanted to use another database product too, so it's more portable. It may also be faster especially with a larger data set--but I don't know because I don't do complicated SQL to know the difference. So now you have a SQL solution and a Perl solution. Is that cool or what! Enjoy! #!/usr/bin/perl ################################################################################ =begin program: sortdigits.pl usage: sortdigits.pl file=my-data-file example: ./sortdigits.pl file=sample-data.txt requires: CGI.pm if you don't have CGI, install it: perl -MCPAN -e 'install CGI' or hardcode the filename instead of using CGI: $file = "my-data-file" ; or use argv: $file = $ARGV[0] ; =cut ################################################################################ use CGI ; $co = new CGI ; $file = $co->param("file") ; ## ## use this if you don't have CGI installed ## just comment-out the above CGI code and ## uncomment this line: ## ## $file = $ARGV[0] ; ## ## then use the program thusly: ## ## usage: ./sortdigits.pl my-data-file ## $rowid = 0 ; open ( FILE, "<$file" ) ; my %data_hash ; my $rowid ; ## ## first load up our hash called $data_hash with values ## while ( <FILE> ) { ++$rowid ; $padded_count = sprintf "%4.4d", $rowid ; $raw_val = $_ ; ## ## take off carriage-returns and line-feeds ## $raw_val =~ /\\r$/ ; $raw_val =~ /\\n$/ ; chomp $raw_val ; ## ## pad our values with leading zeros ## $v = sprintf ( "%05.2lf", $raw_val ) ; $key = "${v}_${padded_count}" ; ## ## load our hash to be sorted ## $data_hash{$key} = "$v\\|$raw_val|$padded_count" ; ## print "hash loading -- $data_hash{$key}\\n" ; ## } close ( FILE ) ; print "\\n\\n-- the sorted list --\\n\\n" ; ## ## generate the sorted list and print it out ## printf ( "%10s -- %10s -- %s \\n", "Padded", "Original", "Rowid" ) ; foreach $key ( sort(keys(%data_hash) ) ) { $value_string = $data_hash{$key} ; ## printf ( "%s\\n", $value_string ) ; my @col_arr = split /\\|/, $value_string ; my $c_value = sprintf ( "%s", $col_arr[0] ) ; my $c_raw = sprintf ( "%s", $col_arr[1] ) ; my $counter = $col_arr[2] ; my $c_key = sprintf ( "%s", $key ) ; chomp $c_raw ; printf ( "%10s -- %10s -- %5s \\n", $c_value, $c_raw, $counter ) ; } exit ; ./sortdigits.pl file=sortdigits.data.txt > sortdigits.output.txt hash loading -- 10.50|10.5|0001 hash loading -- 10.50|10.50|0002 hash loading -- 07.11|7.11|0003 hash loading -- 07.23|7.23|0004 hash loading -- 07.24|7.24|0005 hash loading -- 07.29|7.29|0006 hash loading -- 07.34|7.34|0007 hash loading -- 07.40|7.4|0008 hash loading -- 07.40|7.40|0009 hash loading -- 07.45|7.45|0010 hash loading -- 07.50|7.5|0011 hash loading -- 08.11|8.11|0012 hash loading -- 08.23|8.23|0013 hash loading -- 08.24|8.24|0014 hash loading -- 08.29|8.29|0015 hash loading -- 08.34|8.34|0016 hash loading -- 08.40|8.4|0017 hash loading -- 08.40|8.40|0018 hash loading -- 08.45|8.45|0019 hash loading -- 08.50|8.5|0020 -- the sorted list -- Padded -- Original -- Rowid 07.11 -- 7.11 -- 0003 07.23 -- 7.23 -- 0004 07.24 -- 7.24 -- 0005 07.29 -- 7.29 -- 0006 07.34 -- 7.34 -- 0007 07.40 -- 7.4 -- 0008 07.40 -- 7.40 -- 0009 07.45 -- 7.45 -- 0010 07.50 -- 7.5 -- 0011 08.11 -- 8.11 -- 0012 08.23 -- 8.23 -- 0013 08.24 -- 8.24 -- 0014 08.29 -- 8.29 -- 0015 08.34 -- 8.34 -- 0016 08.40 -- 8.4 -- 0017 08.40 -- 8.40 -- 0018 08.45 -- 8.45 -- 0019 08.50 -- 8.5 -- 0020 10.50 -- 10.5 -- 0001 10.50 -- 10.50 -- 0002
>>>After having seen this problem go unsolved, and after all the people attempting to solve it, I thought I'd give you my .02. >> I solved it on the first day with the mantissa, notmantissa procedures. >Well, you and others are certainly better at SQL than me, I never was good at SQL. SQL for Smarties by Joe Celko. He has many examples of very smart people coming up with very great solutions.