Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql group by and order by clause

Re: sql group by and order by clause

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Fri, 27 Apr 2001 22:28:02 +0200
Message-ID: <3AE9D652.AD66C23B@0800-einwahl.de>

You may try by changing nls_sort to something different than binary. Example (the strange character is 'umlaut a' in German which is sorted as 'ae' when using xgerman sort order and as 8-bit character ordered behind any 7-bitters binarily):

SQL> create table testtab (

  2          a varchar2 (6)
  3          , b varchar2 (6)

  4 )
  5 /

Table created.

SQL>
SQL> insert into testtab (a, b) values ('aaaaaa', 'bbbbbb');

1 row created.

SQL> insert into testtab (a, b) values ('õõõõõõ', 'bbbbbb');

1 row created.

SQL> insert into testtab (a, b) values ('aeaeae', 'bbbbbb');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter session set nls_sort = XGERMAN;

Session altered.

SQL>
SQL> select a, b from testtab group by a, b; aaaaaa bbbbbb
aeaeae bbbbbb
ääääää bbbbbb

SQL> select a, b from testtab group by a, b order by a, b; aaaaaa bbbbbb
ääääää bbbbbb
aeaeae bbbbbb

Mind that this will not always work because group by does not guarantee any sort order.

Martin Received on Fri Apr 27 2001 - 15:28:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US