Re: Getting the max of a char field

From: Karsten Holm <holm.karsten_at_get2net.dk>
Date: Fri, 26 Nov 1999 23:06:23 +0100
Message-ID: <7wD%3.789$Bw.1076@news.get2net.dk>

Hi
c1 probably contains non-numerical chars as an example "a1" and you can't to_number(a1).

It's difficult to tell which max you want.

If the integer is the leading character and all the first characters are numbers you could try

select max(to_number(substr(c,1,1))
but it will fail if just one row is not containing a number as the first character
hope this can help you
/Karsten
Franz Mueller wrote in message <383e49c7.1610726_at_news.salink.net>...
>Hi,
>
>I have a table T1 and column c1 that is a char, but contains integers.
>What I want, is to get the max of these values:
>"select to_num(c1) from T1" gives me the integer values
>"select max(to_num(c1)) from T1" or even "select max(NUM) from (select
>to_num(c1) NUM from T1)" gives me an error:
>"ORA-01722: invalid number"
>
>Has anybody any idea how to get this max?
>Thanks
Received on Fri Nov 26 1999 - 16:06:23 CST