Thread: interesting finding on order by behaviour
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
On Fri, 2011-07-22 at 10:11 -0700, Samuel Hwang wrote:
perhaps http://www.postgresql.org/docs/9.1/static/charset.html will provide an answer
I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL 9.0.4 and found something interesting... results ===== SQL Server 2008 R2 (with case insensitive data, the ordering follows ASCII order) Oracle 10 (data is case-sensitive, the ordering follows ASCII order) PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ... DIFFERENT)
perhaps http://www.postgresql.org/docs/9.1/static/charset.html will provide an answer
On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote: > results > ===== > SQL Server 2008 R2 (with case insensitive data, the ordering follows > ASCII order) > > f1 > --- > AbC > abc > ABc > cde > CdE Well, if it's case insensitive, then AbC & abc & ABc are all equal, so any order for those 3 would be correct... -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
On Jul 22, 12:20 pm, scott_r...@elevated-dev.com (Scott Ribe) wrote: > On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote: > > > results > > ===== > > SQL Server 2008 R2 (with case insensitive data, the ordering follows > > ASCII order) > > > f1 > > --- > > AbC > > abc > > ABc > > cde > > CdE > > Well, if it's case insensitive, then AbC & abc & ABc are all equal, so any order for those 3 would be correct... > > -- > Scott Ribe > scott_r...@elevated-dev.comhttp://www.elevated-dev.com/ > (303) 722-0567 voice > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Sorry I didn't make it clear, the interesting part is how PostgreSQL sorts data. The server encoding is set to UTF8 and collation is united states.1252 The client encoding is Unicode.
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
Thanks for the info. That clarify things :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/interesting-finding-on-order-by-behaviour-tp4623884p4632301.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.