Oracle 最大和订单工作不同

askTom 2017-03-22

135

问题描述

你好，

select rn, code, max(code) over () as mcode from
(
select 1 as rn, '1C538454-D89A-E211-88D1-1CC1DEE781E4' as code from dual
    union all
select 2 as rn, 'C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4' as code from dual
)
order by code asc

result:
RN CODE MCODE
2 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4
1 1C538454-D89A-E211-88D1-1CC1DEE781E4 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4复制

为什么ASC的ORDER首先得到最大的代码值？

谢谢!

专家解答

这不是我在使用11.2.0.4时看到的行为，结果按代码升序排列，如您所料:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>
SQL> select rn, code, max(code) over () as mcode from
  2  (
  3  select 1 as rn, '1C538454-D89A-E211-88D1-1CC1DEE781E4' as code from dual
  4      union all
  5  select 2 as rn, 'C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4' as code from dual
  6  )
  7  order by code asc;

        RN CODE                                 MCODE
---------- ------------------------------------ ------------------------------------
         1 1C538454-D89A-E211-88D1-1CC1DEE781E4 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4
         2 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4 C5FAEF8F-D1B0-E211-A69F-1CC1DEE781E4
复制

所以:

-你到底用的是什么版本？
-您使用什么查看结果？
-获取数据后，您的客户是否可能对数据进行排序？