I get different sorting results when trying the same select on two different clients.
This is a simple example:
select 'CPSC' from dual
union
select 'C1' from dual
order by 1 asc
I get ‘CPSC’ first, and my colleague gets ‘C1’ first.
We are connected to the same database instance with the same user. Two different PC’s, running the query in Toad on Windows.
Any ideas as to why there could be a different results is appreciated.
1
The order is controlled by the NLS_SORT session parameters and you can see the values for your respective sessions using:
SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter IN ('NLS_SORT', 'NLS_COMP')
You will probably find that one of you has something like:
| PARAMETER | VALUE |
|---|---|
| NLS_SORT | BINARY |
| NLS_COMP | BINARY |
And the other has different values.
You can see the difference if you set different sort methods on the query:
WITH data (value) AS (
SELECT 'CPSC' FROM DUAL UNION ALL
SELECT 'C1' FROM DUAL
)
SELECT *
FROM data
ORDER BY NLSSORT(value, 'NLS_SORT = BINARY')
Outputs:
| VALUE |
|---|
| C1 |
| CPSC |
and:
WITH data (value) AS (
SELECT 'CPSC' FROM DUAL UNION ALL
SELECT 'C1' FROM DUAL
)
SELECT *
FROM data
ORDER BY NLSSORT(value, 'NLS_SORT = XFRENCH')
Outputs:
| VALUE |
|---|
| CPSC |
| C1 |
Use:
ALTER SESSION SET NLS_SORT = BINARY;
(replace BINARY with whatever you require)
So that both sessions use the same collation key for sorting.
If you cannot manually manage it via setting the session parameter then you could:
- Specify the collation in the query using the
NLSSORTfunction; or - Create a logon trigger to always set the
NLS_SORTsession parameter at the start of a user’s session so that all users (initially) use the same value.
fiddle
You can try running this query:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_SORT';
If you and your colleague have a different result then this may be the problem.
Hope this helps!
1
Right; looks like NLS_SORT has something to do with that. Documentation says that
NLS_SORT specifies the collating sequence for ORDER BY queries.
Syntax is
NLS_SORT = { BINARY | linguistic_definition }
- If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
- If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.
These are valid values:
SELECT value FROM V$NLS_VALID_VALUES WHERE parameter = 'SORT' order by 1;
Let’s try it:
SQL> alter session set nls_sort = binary;
Session altered.
SQL> select 'CPSC' from dual
2 union
3 select 'C1' from dual
4 order by 1 asc
5 /
'CPS
----
C1
CPSC
SQL> alter session set nls_sort = croatian;
Session altered.
SQL> select 'CPSC' from dual
2 union
3 select 'C1' from dual
4 order by 1 asc
5 /
'CPS
----
CPSC
C1
SQL>
TOAD itself, as far as I can tell, doesn’t have that option included into its “Options”, so I presume that you (or your colleagues) have modified it manually.
1