Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql group by and order by clause
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)
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