Sorting numeric data stored as char (numeric vs. lexical)
Sorting numeric data stored as char (numeric vs. lexical) I have numeric data stored in a char-column. The data is NOT zero-padded. The question is, how do I retrieve the data in sorted order (actually, I just want the LAST record) As you can imagine, the problem is that numerically, 100 > 99, but lexically 99 > 100 I have the option of zero-padding all the data Vince Sent via Deja.com http://www.deja.com/ Before you buy.
(Yeah, I know it is *rude* to followup your own posts,
but I wanted to head-off any extra effort on your behalf...
With much thanks to Madison of Informix.com,
here is the syntax that solved the problem:
select * from tab1 order by 0+col1
In article <7snvtk$cqb$1@nnrp1.deja.com>,
VP <pachiano@writeme.com> wrote:
> Sorting numeric data stored as char (numeric vs. lexical)
>
> I have numeric data stored in a char-column.
> The data is NOT zero-padded.
>
> The question is, how do I retrieve the data in
> sorted order (actually, I just want the LAST record)
>
> As you can imagine, the problem is that
> numerically, 100 > 99, but lexically 99 > 100
>
> I have the option of zero-padding all the data
>
Sent via Deja.com http://www.deja.com/
Before you buy.
VP wrote:
>
> Sorting numeric data stored as char (numeric vs. lexical)
>
> I have numeric data stored in a char-column.
> The data is NOT zero-padded.
>
> The question is, how do I retrieve the data in
> sorted order (actually, I just want the LAST record)
>
> As you can imagine, the problem is that
> numerically, 100 > 99, but lexically 99 > 100
>
> I have the option of zero-padding all the data
CREATE TEMP TABLE for_sorting ( sortcol integer, lex_column );
INSERT INTO for_sorting SELECT lex_column, lex_column FROM orig_table;
SELECT o.*
FROM orig_table o
WHERE lex_column = (
SELECT lex_column
FROM for_sorting
WHERE sortcol = MAX(sortcol) );
DROP TABLE for_sorting;
Art S. Kagel