MAX function with VARCHAR2 field [message #21064] |
Wed, 10 July 2002 04:12 |
Tim
Messages: 49 Registered: October 2000
|
Member |
|
|
Why does this happen?
SQL> SELECT ID FROM LOCATIONS;
ID
---
8
9
10
SQL> SELECT MAX(ID) FROM LOCATIONS;
MAX(ID)
-------
9
Shouldn't the answer be 10? (TUBEID is a VARCHAR2 field)
thanks,
Tim
|
|
|
|
Re: MAX function with VARCHAR2 field [message #21069 is a reply to message #21064] |
Wed, 10 July 2002 09:36 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You can always use the to_number function to derive the maximum numerical value:
sql>select * from t;
C
--
1
2
3
10
20
8
9
7 rows selected.
sql>select max(c) from t;
MA
--
9
sql>select max(to_number(c)) from t;
MAX(TO_NUMBER(C))
-----------------
20
|
|
|