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;
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 |