Home » SQL & PL/SQL » SQL & PL/SQL » MAX() Returning Wrong Values (APEX SQL Command on Oracle 10gR2 on Linux)
MAX() Returning Wrong Values [message #361516] Wed, 26 November 2008 13:08 Go to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Hi There,
I'm having a problem determining why MAX() will not return a value greater than 999, yes there are values greater than that in the table in question... Here is the SQL... note that in the second statement the correct values are returned.

select max(monkey) from
(select LTRIM("unique_monkey", 'monkey_' ) as monkey
from table1 )
where monkey > 10;
--Produces a result of 999 as the MaxValue

select max(monkey) from
(select LTRIM("unique_monkey", 'monkey_' ) as monkey
from table1 )
where monkey > 1000;
--Produces a result of 3481 (which is the correct max value for the underlying table).

I've tried to recreate this problem but to no avail... meaning this problem is specific to this table.

The column in question is varchar2(20), note that none of the values within exceed even 11 characters.

Thank you...

Re: MAX() Returning Wrong Values [message #361519 is a reply to message #361516] Wed, 26 November 2008 13:22 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@greendba,

Hope this example makes things clear for you:
SQL> WITH test_tab AS
  2       (SELECT '999' col_1
  3          FROM DUAL
  4        UNION ALL
  5        SELECT '1000'
  6          FROM DUAL)
  7  SELECT MAX (col_1) MAX_Digit
  8    FROM test_tab;

MAX_
----
999

SQL> WITH test_tab AS
  2       (SELECT '999' col_1
  3          FROM DUAL
  4        UNION ALL
  5        SELECT '1000'
  6          FROM DUAL)
  7  SELECT MAX (TO_NUMBER (col_1)) MAX_Digit
  8    FROM test_tab;

 MAX_DIGIT
----------
      1000



Please understand that the string '9' is greater than string '1000'. Max() Function is giving the correct output. Only you have the concepts wrong.

[***Added More examples For Your Understanding ]
String relative comparison (equality excluded) is done mainly using the String's ascii value.

SQL> SELECT ASCII ('100000')
  2    FROM DUAL;

ASCII('100000')
---------------
             49

SQL>   
SQL> SELECT ASCII ('1')
  2    FROM DUAL;

ASCII('1')
----------
        49

SQL>   
SQL> SELECT ASCII ('1111')
  2    FROM DUAL;

ASCII('1111')
-------------
           49

SQL> 
SQL> SELECT ASCII ('9')
  2    FROM DUAL;

ASCII('9')
----------
        57

SQL>   
SQL> SELECT ASCII ('999999')
  2    FROM DUAL;

ASCII('999999')
---------------
             57

SQL>   
SQL> SET FEEDBACK ON;
SQL> SELECT 'ABCDE' col_1 FROM DUAL
  2  where '9' < '1111';

no rows selected

SQL> SELECT 'ABCDE' col_1 FROM DUAL
  2  WHERE '1111' < '9';

COL_1
-----
ABCDE

1 row selected.


Hope this helps.

Regards,
Jo

[Updated on: Wed, 26 November 2008 13:52]

Report message to a moderator

Re: MAX() Returning Wrong Values [message #361525 is a reply to message #361519] Wed, 26 November 2008 14:02 Go to previous messageGo to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Thanks Jo...

I came to the same resolution as well...
But now I understand why!

Thank You!
Re: MAX() Returning Wrong Values [message #361527 is a reply to message #361525] Wed, 26 November 2008 14:28 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@greendba,

I think you will find the quote from Oracle Documentation interesting. Forgot to add it in my previous post.

Quote:

Character String Values

Character values are compared using one of these comparison rules:

Blank-padded comparison semantics
Nonpadded comparison semantics



Read more in this Link: Datatype Comparison Rules

Regards,
Jo
Previous Topic: ORCL 28009
Next Topic: Multiple Selects One Report
Goto Forum:
  


Current Time: Fri Dec 09 00:01:41 CST 2016

Total time taken to generate the page: 0.08964 seconds