Home » SQL & PL/SQL » SQL & PL/SQL » MAX function with VARCHAR2 field
MAX function with VARCHAR2 field [message #21064] Wed, 10 July 2002 04:12 Go to next message
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 #21065 is a reply to message #21064] Wed, 10 July 2002 04:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Max function is supposed to be used on numeric data. NOT on varchar data.
if used, it will give only the maximum First digit value
SQL> desc loc
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------------
 ID                                                             VARCHAR2(10)
SQL> select * from loc;

ID
----------
1
1
12
1
8
9
10

7 rows selected.

SQL> select max(id) from loc;

MAX(ID)
----------
9

SQL> create table loc1
  2  (id number);

Table created.

SQL> insert into loc1 select * from loc;

7 rows created.

SQL> select * from loc1;

        ID
----------
         1
         1
        12
         1
         8
         9
        10

7 rows selected.

SQL> select max(id) from loc1;

   MAX(ID)
----------
        12
SQL> delete from loc where id=9;

1 row deleted.

SQL> select max(id) from loc;

MAX(ID)
----------
8
Re: MAX function with VARCHAR2 field [message #21069 is a reply to message #21064] Wed, 10 July 2002 09:36 Go to previous message
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
Previous Topic: constraint question
Next Topic: Creating bi birectional table?
Goto Forum:
  


Current Time: Thu Mar 28 17:29:07 CDT 2024