Different sort order result on two different clients

Question

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.

Answer 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 NLSSORT function; or
• Create a logon trigger to always set the NLS_SORT session parameter at the start of a user's session so that all users (initially) use the same value.

fiddle