Oracle意外排序行为

Question

考虑以下内容:

CREATE TABLE TestPersons
(
PersonID int,
Name varchar(255),
FirstName varchar(255),
Address varchar(255)
)
insert into TestPersons(PersonID, Name, FirstName , Address) Values (1, 'name1', 'firstname1', 'adress1');
insert into TestPersons(PersonID, Name, FirstName , Address) Values (2, 'name2', 'firstname2', 'adress2');
insert into TestPersons(PersonID, Name, FirstName , Address) Values (3, 'name3', 'firstname3', 'adress3');
insert into TestPersons(PersonID, Name, FirstName , Address) Values (4, 'name4', 'firstname4', 'adress4');

第一个查询(正常):

SELECT name, firstname, personid, count(name), row_number() over (ORDER BY name, firstname) as rn
FROM testpersons
GROUP BY name, firstname, personid

的回报:

name1   firstname1  1   1   1
name2   firstname2  2   1   2
name3   firstname3  3   1   3
name4   firstname4  4   1   4

Second Query (unexpected result):

SELECT name, firstname, personid, count(name), row_number() over (ORDER BY name desc, firstname desc) as rn
FROM testpersons
GROUP BY name, firstname, personid

返回所有行name4和firstname4:

name4   firstname4  4   1   1
name4   firstname4  3   1   2
name4   firstname4  2   1   3
name4   firstname4  1   1   4

我希望第一次查询的结果顺序改变。

知道为什么吗?

---

select * from v$version的输出如下:

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production   0
PL/SQL Release 12.1.0.1.0 - Production  0
"CORE   12.1.0.1.0  Production" 0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production  0

Answer 1

我不知道你在说什么。下面是从我的会话中直接复制和粘贴的，在那里我输入了您发布的命令。我从第二个查询中得到了正确的结果。

你有什么事没告诉我们吗?您使用的是什么版本的Oracle ?你是如何与它互动的?你的前端是什么?你是直接发送你的查询还是通过一些应用程序?

你看到的是Oracle 11.2 XE, SQL*Plus前端。

SQL> CREATE TABLE TestPersons
  2  (
  3  PersonID int,
  4  Name varchar(255),
  5  FirstName varchar(255),
  6  Address varchar(255)
  7  );
Table created.
SQL> insert into TestPersons(PersonID, Name, FirstName , Address) Values (1, 'name1', 'firstname1', 'adress1');
1 row created.
SQL> insert into TestPersons(PersonID, Name, FirstName , Address) Values (2, 'name2', 'firstname2', 'adress2');
1 row created.
SQL> insert into TestPersons(PersonID, Name, FirstName , Address) Values (3, 'name3', 'firstname3', 'adress3');
1 row created.
SQL> insert into TestPersons(PersonID, Name, FirstName , Address) Values (4, 'name4', 'firstname4', 'adress4');
1 row created.
SQL> column name format a10
SQL> column firstname format a18
SQL> SELECT name, firstname, personid, count(name), row_number() over (ORDER BY name, firstname) as rn
  2  FROM testpersons
  3  GROUP BY name, firstname, personid;
NAME       FIRSTNAME            PERSONID COUNT(NAME)         RN
---------- ------------------ ---------- ----------- ----------
name1      firstname1                  1           1          1
name2      firstname2                  2           1          2
name3      firstname3                  3           1          3
name4      firstname4                  4           1          4
4 rows selected.
SQL> SELECT name, firstname, personid, count(name), row_number() over (ORDER BY name desc, firstname desc) as rn
  2  FROM testpersons
  3  GROUP BY name, firstname, personid;
NAME       FIRSTNAME            PERSONID COUNT(NAME)         RN
---------- ------------------ ---------- ----------- ----------
name4      firstname4                  4           1          1
name3      firstname3                  3           1          2
name2      firstname2                  2           1          3
name1      firstname1                  1           1          4
4 rows selected.