Home » SQL & PL/SQL » SQL & PL/SQL » Difference in SQL 'order by' and MIN, MAX, LISTAGG results (oracle 12c, client 11G sous windows)
Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666885] |
Thu, 30 November 2017 07:44 |
|
moracles
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Hi,
could anyone help me to understand, why the 'order by' result is not the same in my test here :
select x from (select 'AA' x from dual union select 'A_' x from dual) order by x ASC;
produces expected result: 'A_' before 'AA'
while
select
min(x) "min_x"
, max(x) "max_x"
, listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
produces unexpected result : min_x='AA', max_x='A_' and liste_x='AA # A_'
Regards
[Updated on: Thu, 30 November 2017 07:47] Report message to a moderator
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666886 is a reply to message #666885] |
Thu, 30 November 2017 07:49 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
orclx> select x from (select 'AA' x from dual union select 'A_' x from dual) order by x ASC;
X
--
AA
A_
orclx>
orclx>
orclx>
orclx> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
5 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
mi ma
-- --
liste_x
--------------------------------------------------------------------------------------------------------------
AA A_
AA # A_
orclx>
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666888 is a reply to message #666886] |
Thu, 30 November 2017 08:13 |
|
moracles
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Hi,
why is it different for me :
SQL> COLUMN x FORMAT A10
SQL> COLUMN min_x FORMAT A10
SQL> COLUMN max_x FORMAT A10
SQL> COLUMN liste_x FORMAT A10
SQL>
SQL> select x from (select 'AA' x from dual union select 'A_' x from dual) order by x ASC;
X
----------
A_
AA
SQL> --
SQL> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
5 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
min_x max_x liste_x
---------- ---------- ----------
AA A_ AA # A_
SQL> spool off
Does it depend on any database parameter , (NLS...) ?
Regards
[Updated on: Thu, 30 November 2017 08:17] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666899 is a reply to message #666898] |
Thu, 30 November 2017 09:57 |
|
moracles
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string €
nls_date_format string DD/MM/RR
nls_date_language string
nls_dual_currency string
nls_iso_currency string FRANCE
nls_language string FRENCH
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string ,
nls_sort string FRENCH
nls_territory string FRANCE
nls_time_format string
nls_timestamp_format string DD/MM/RR HH24:MI:SSXFF
nls_timestamp_tz_format string DD/MM/RR HH24:MI:SSXFF TZR
nls_time_tz_format string
[Updated on: Thu, 30 November 2017 10:01] Report message to a moderator
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666901 is a reply to message #666900] |
Thu, 30 November 2017 10:18 |
|
moracles
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Indeed, it has changed, but isn't it the opposite of expected ('A_' should be before 'AA' ) ?
SQL> alter session set nls_sort=BINARY;
Session altered.
SQL> select x from (select 'AA' x from dual union select 'A_' x from dual) order by x ASC;
X
----------
AA
A_
SQL> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
5 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
min_x max_x liste_x
---------- ---------- ----------
AA A_ AA # A_
[Updated on: Thu, 30 November 2017 10:24] Report message to a moderator
|
|
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666906 is a reply to message #666903] |
Thu, 30 November 2017 14:39 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Database SQL Language Reference
Sorting Query Results
Quote:The mechanism by which Oracle Database sorts character values for the ORDER BY clause, also known as the collation, is specified by the NLS_SORT session parameter.
So, the following result is correct for French:
SQL> alter session set nls_sort=french;
Session altered.
SQL> select x from (select 'AA' x from dual union select 'A_' x from dual) order by x ASC;
X
--
A_
AA
2 rows selected.
Now, the first reason that LISTAGG does not use this parameter is explained in Database Globalization Support Guide, Chapter 5 Linguistic Sorting and Matching, Section Performing Linguistic Comparisons about NLS_COMP:
Quote:BINARY: All SQL collations and comparisons are based on the binary values of the string characters, regardless of the value set to NLS_SORT.
Now even if you set it to LINGUISTIC as advised ("When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT."):
SQL> alter session set nls_comp=linguistic;
Session altered.
SQL> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 , listagg(x, ' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
5 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
mi ma
-- --
liste_x
-----------------------------------------------------------------------------------------
A_ AA
AA # A_
The reason is just below the former quote in "Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" which lists when this parameter is taken into account, and LISTAGG with its "WITHIN GROUP (ORDER BY)" clause is not there.
Is this a bug? Well, for Oracle, it works as documented.
Now, the question is why MIN and MAX in OP's output give the expected value given the NLS_SORT and NLS_COMP values he posted, I didn't get them in the same way but in the expected given the former table:
SQL> alter session set nls_sort=french nls_comp=binary;
Session altered.
SQL> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
mi ma
-- --
AA A_
1 row selected.
(Note: I did this in 11.2.0.4.170418)
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666908 is a reply to message #666907] |
Thu, 30 November 2017 14:55 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And the same thing from a 11g client to a 12c database:
>sqlplus michel/michel@mikc
SQL*Plus: Release 11.2.0.4.0 Production on Jeu. Nov. 30 21:53:42 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @v
Oracle version: 12.1.0.2.160719
SQL> alter session set nls_sort=french nls_comp=binary;
Session altered.
SQL> select
2 min(x) "min_x"
3 , max(x) "max_x"
4 from (select x from (select 'AA' x from dual union select 'A_' x from dual)) s ;
mi ma
-- --
AA A_
1 row selected.
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666912 is a reply to message #666908] |
Fri, 01 December 2017 02:31 |
|
moracles
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Michel,
my default NLS are : NLS_SORT=FRENCH and NLS_COMP=BINARY.
In #666906 you say : Quote:"Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" which lists when this parameter is taken into account, and LISTAGG with its "WITHIN GROUP (ORDER BY)" clause is not there
The LISTAGG function is not explicitly in the table "5-3", but it's an analytic function with "OVER(ORDER BY)" isn't it ?
So, for me, according to the "Table 5-3 Linguistic Comparison Behavior with NLS_COMP Settings" ->"Analytic Function Clauses" -> "OVER(ORDER BY)"
The result of LISTAGG ("Honors NLS_SORT"), should be "A_ # AA", regardless of NLS_COMP value.
What about my solution, which consists of working with NLS_SORT=BINARY ?
Regards
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666916 is a reply to message #666912] |
Fri, 01 December 2017 02:57 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:my default NLS are : NLS_SORT=FRENCH and NLS_COMP=BINARY.
So you should have the same results than those I posted.
Note: he would be easier to see what you have if you post it.
Quote:The LISTAGG function is not explicitly in the table "5-3", but it's an analytic function with "OVER(ORDER BY)" isn't it ?
No, LISTAGG has no "OVER(ORDRE BY)" clause.
Quote:What about my solution, which consists of working with NLS_SORT=BINARY ?
It will give you a binary sort, it is good if this is what you want, it is not if you want a linguistic sort.
So only you can define what you want.
But anyway LISTAGG will give a binary sort within each row of the result.
Or you could write your own LISTAGG function, search on the board for STRAGG you will find several alternate codes for it.
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666928 is a reply to message #666885] |
Fri, 01 December 2017 09:06 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
So you want LISTAGG to use French ordering, right? Then orader by NLSSORT:
SQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
SQL> show parameter nls_comp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string BINARY
SQL> select min(x) "min_x",
2 max(x) "max_x",
3 listagg(x,' # ') WITHIN GROUP (ORDER BY x ASC) "liste_x"
4 from (
5 select 'AA' x from dual union all
6 select 'A_' x from dual
7 )
8 /
mi ma liste_x
-- -- ---------------
AA A_ AA # A_
SQL> select min(x) "min_x",
2 max(x) "max_x",
3 listagg(x,' # ') WITHIN GROUP (ORDER BY NLSSORT(x,'NLS_SORT=FRENCH')) "liste_x"
4 from (
5 select 'AA' x from dual union all
6 select 'A_' x from dual
7 )
8 /
mi ma liste_x
-- -- ---------------
AA A_ A_ # AA
SQL>
SY.
|
|
|
|
|
Re: Difference in SQL 'order by' and MIN, MAX, LISTAGG results [message #666931 is a reply to message #666929] |
Fri, 01 December 2017 10:20 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:P.S.
min_x=AA and max_x=A_ is wrong result
It is correct as BINARY is used.
Quote:All I need, is to obtain the same result from 'select ... order by' and LISTAGG in PLSQL procedure.
Will your client accept the following result?
SQL> alter session set nls_sort=binary;
Session altered.
SQL> select x from t order by x;
X
-----
A
E
I
U
Z
a
e
i
u
z
é
é
è
è
î
î
û
Solomon's solution would be a correct one (given your standard settings for NLS_COMP and NLS_SORT) but there are numerous bugs when using NLSSORT and NLS_SORT with analytic functions from error ORA-04030 to wrong results, have a look in MOS. Before using it, I'd ask Oracle to guarantee that all known bugs in this matter are fixed for the versions you use and will use.
[Updated on: Fri, 01 December 2017 10:28] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:26:08 CDT 2024
|