Início / dba / Perguntas / 331880
Accepted
Andy DB Analyst
Asked: 2023-10-05 22:43:42 +0800 CST

Oracle MAX fornece resultado diferente de ORDER BY desc

  • 772

Ao tentar classificar alguns valores hexadecimais, obtive resultados diferentes para agregação MAX() e classificação ORDER BY desc:

with some_data as
(
    select '123A55' as hex
    from dual
    union all
    select '123255' as hex
    from dual
)

select max(hex)
from some_data
;

Result: 123A55

with some_data as
(
    select '123A55' as hex
    from dual
    union all
    select '123255' as hex
    from dual
)

select *
from some_data
order by hex desc
;

Result:
123255
123A55

No banco de dados MySQL dá 123A55 em ambos os casos.

Existem regras diferentes no ORACLE para ordem de classificação para MAX() MIN() e ORDER BY?

oracle

1 respostas

  1. Best Answer

    O resultado depende das suas configurações ambientais. Exemplo:

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by hex desc;

HEX   
------
123A55
123255

    Mas:

    alter session set NLS_SORT='GERMAN';

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by hex desc;

HEX   
------
123255
123A55

    MAX não é afetado pela configuração acima neste caso - mesma sessão:

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select max(hex) from some_data;

MAX(HE
------
123A55

    Mas definindo outro valor na mesma sessão:

    alter session set NLS_COMP = 'LINGUISTIC';

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select max(hex) from some_data;

MAX(HE
------
123255

    As configurações acima podem ser especificadas mesmo no nível da instrução.

    with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by NLSSORT(hex, 'NLS_SORT = BINARY') desc;

HEX   
------
123A55
123255

with some_data as
( select '123A55' as hex from dual union all
  select '123255' as hex from dual)
select * from some_data order by NLSSORT(hex, 'NLS_SORT = GERMAN') desc;

HEX   
------
123255
123A55
    • 2

relate perguntas