MIN/MAX aggregate function with STRING in ORACLE

Question

I don't understand the way Oracle do the sorting of a string with MIN/MAX. I guess, that small letter(a, b, c) should be returned before the big one (A, B, C). That's why my expectation is to get as MIN - "bob" - small b is "smaller" than big "J". With MAX opposite of it.

I'm getting in Postgres and Sql Server correct result. Is it caused due to some collation/language/character set differences?

  DROP TABLE test;
  CREATE TABLE test (a INT, name VARCHAR2(10));
   INSERT INTO test(a, name) 
       VALUES (1, 'John')
            , (2, 'bob')
       ;
  COMMIT;
  
  
  SELECT MIN(name) -- return John instead of bob
       , MAX(name) -- return bob instead of John
   FROM test;

My Oracle 23c XE configuration:

SELECT *
  FROM V$NLS_PARAMETERS;

Answer 1

If you do:

SELECT t.*, ASCII(SUBSTR(name, 1, 1)) FROM test t;

Then the output is:

A	NAME	ASCII(SUBSTR(NAME,1,1))
1	John	74
2	bob	98

And you can see than the ASCII code for J is 74 and b is 98 so comparing the underlying byte-values then 74 < 98 so J < b.

If you have:

ALTER SESSION SET NLS_COMP = 'BINARY';
ALTER SESSION SET NLS_SORT = 'BINARY';
SELECT MIN(name), MAX(name) FROM test;

Then the output is:

MIN(NAME)	MAX(NAME)
John	bob

and you are comparing the values as binary.

If you change to a linguistic comparison and set the sort to be case-insensitive:

ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
SELECT MIN(name), MAX(name) FROM test;

Then the output is:

MIN(NAME)	MAX(NAME)
bob	John

And b < J.

---

If you do not want to change the default NLS settings then you can explicitly specify the sort order in the query:

SELECT MIN(name) KEEP (DENSE_RANK FIRST ORDER BY NLSSORT(name, 'NLS_SORT=BINARY_CI'))
         AS min,
       MAX(name) KEEP (DENSE_RANK LAST ORDER BY NLSSORT(name, 'NLS_SORT=BINARY_CI'))
         AS max
FROM   test;

Which outputs:

MIN	MAX
bob	John

fiddle