Forum Stats

  • 3,726,961 Users
  • 2,245,298 Discussions
  • 7,852,510 Comments

Discussions

First value in ORDER BY different from MIN

Marwim
Marwim Member Posts: 3,620 Gold Trophy
edited May 2014 in SQL & PL/SQL

Hello,

according to the docu NLS_SORT should determine the sorting order in ORDER BY and MIN/MAX

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SELECT  *
FROM    nls_session_parameters
WHERE   parameter = 'NLS_SORT'
OR      parameter = 'NLS_COMP';

PARAMETER   VALUE 
----------- -------
NLS_SORT    GERMAN 
NLS_COMP    BINARY

Therefore I expect that I get the same first/min and last/max values from the following statements

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

WITH testdata AS (
    SELECT  'A' c FROM dual UNION ALL
    SELECT  'B' c FROM dual UNION ALL
    SELECT  '0' c FROM dual UNION ALL
    SELECT  '1' c FROM dual UNION ALL
    SELECT  '2' c FROM dual
    )
SELECT  MAX(c)
       ,MIN(c)
FROM    testdata;

MAX(C) MIN(C)
------ ------
B      0     

WITH testdata AS (
    SELECT  'A' c FROM dual UNION ALL
    SELECT  'B' c FROM dual UNION ALL
    SELECT  '0' c FROM dual UNION ALL
    SELECT  '1' c FROM dual UNION ALL
    SELECT  '2' c FROM dual
    )
SELECT  c
       ,ASCII(c)
FROM    testdata
ORDER BY c;

C   ASCII(C)
- ----------
A         65
B         66
0         48
1         49
2         50

So I read about sorting

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

ALTER SESSION SET NLS_SORT = 'BINARY';

C   ASCII(C)
- ----------
0         48
1         49
2         50
A         65
B         66

Now I get the expected output, but I still cannot explain why a linguistic sort orders letters before digits and why it is different for ORDER BY and MAX/MIN.

Regards

Marcus

Best Answer

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited May 2014 Accepted Answer
    according to the docu NLS_SORT should determine the sorting order in ORDER BY and MIN/MAX
    
    

    Correct, but you overlooked this part :

    The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.
    
    

    In your example, MIN and MAX always use the BINARY collation, unless NLS_SORT is set to a specific language and NLS_COMP is set to LINGUISTIC.

    On the contrary, ORDER BY always obeys the NLS_SORT param regardless of the NLS_COMP param.

    This summarizes which operator and SQL clause obeys which parameter :

    Linguistic Sorting and String Searching

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,403

    You don't say your Oracle version. If it's a least 10gR2, look at nls_comp=linguistic.

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited May 2014 Accepted Answer
    according to the docu NLS_SORT should determine the sorting order in ORDER BY and MIN/MAX
    
    

    Correct, but you overlooked this part :

    The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.
    
    

    In your example, MIN and MAX always use the BINARY collation, unless NLS_SORT is set to a specific language and NLS_COMP is set to LINGUISTIC.

    On the contrary, ORDER BY always obeys the NLS_SORT param regardless of the NLS_COMP param.

    This summarizes which operator and SQL clause obeys which parameter :

    Linguistic Sorting and String Searching

  • Marwim
    Marwim Member Posts: 3,620 Gold Trophy
    You don't say your Oracle version. If it's a least 10gR2, look at nls_comp=linguistic.
    

    It is there, three times :-)

  • Marwim
    Marwim Member Posts: 3,620 Gold Trophy
    This summarizes which operator and SQL clause obeys which parameter :
    Linguistic Sorting and String Searching
    

    This is exactly what I've been looking for.

  • Paul  Horth
    Paul Horth Member Posts: 3,403
    edited May 2014
    Marwim wrote:
    
    
    You don't say your Oracle version. If it's a least 10gR2, look at nls_comp=linguistic.
    
    It is there, three times :-)
    
    

    Sorry, was looking at text, not screen shot headers! Doh!

This discussion has been closed.