Re: interesting finding on order by behaviour

Samuel Hwang wrote:
> I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
> 9.0.4 and found something interesting...
>
> set up
> =====
> drop table t1
> create table t1 (f1 varchar(100))
> insert into t1 (f1) values ('AbC')
> insert into t1 (f1) values ('CdE')
> insert into t1 (f1) values ('abc')
> insert into t1 (f1) values ('ABc')
> insert into t1 (f1) values ('cde')
>
> test
> ===
> select * from t1 order by f1
> select min(f1) as min, max(f1) as max from t1
>
> results
> =====
> SQL Server 2008 R2 (with case insensitive data, the ordering follows
> ASCII order)
>
> f1
> ---
> AbC
> abc
> ABc
> cde
> CdE
>
> min max
> ------ -------
> AbC CdE
>
> Oracle 10 (data is case-sensitive, the ordering follows ASCII order)
>
> f1
> ---
> ABc
> AbC
> CdE
> abc
> cde
>
> min max
> ------ -------
> ABc cde
>
> PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
> DIFFERENT)
>
> f1
> ---
> abc
> AbC
> ABc
> cde
> CdE
>
> min max
> ------ -------
> abc CdE
>

> The server encoding is set to UTF8 and collation is united states.1252
> The client encoding is Unicode.

I can only speak about Oracle and PostgreSQL.

The problem is that they use different collations.

I don't know what NLS_LANGUAGE is set to in your Oracle session, but I
assume
that it is AMERICAN. You can check with
SELECT value FROM nls_session_parameters WHERE
parameter='NLS_LANGUAGE';

Sorting in Oracle is controled by the NLS_SORT parameter, which by
default
is set to BINARY if NLS_LANGUAGE is AMERICAN, which is why you get ASCII
ordering (in GERMAN, it would be different :^/ ).

PostgreSQL uses the operating system's collation, which in your case
gives
you linguistic ordering.

In Oracle, try something like
ALTER SESSION SET NLS_SORT = 'GENERIC_M';
for a non-binary sorting order, and in PostgreSQL (before 9.1), create
your
database with C collation for binary sorting order.

You can force binary order in PostgreSQL with
SELECT * FROM t1 ORDER BY f USING ~<~;

Yours,
Laurenz Albe