sqloracle-databasesql-order-by

Different sort order result on two different clients

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.

Solution

  • 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:

    fiddle