Order by: numbers after letters?

Why Oracle sorts letters ahead of numbers while Windows does vice versa? How can I change sort order?

select 'English letters'
from   dual
union
select '12345'
from dual
order by 1
VadimJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

srikant033100Commented:
u mean u should have letter first and then number or vice versa

Srikant
VadimJAuthor Commented:
I got used to DOS' and Windows' sorting order. This order corresponds to ASCII table. Numbers' ASCII code are smaller.
rbramhaneCommented:
VadimJ,

I have citied an example for u., please clarify do u regrd ur 1st part of questions...Why Oracle sorts letters ahead of numbers ( I'm not clear ....it actually doesn't do the same ) ? Check this....

SQL> select 'English letters'
  2  from   dual
  3  union
  4  select '12345'
  5  from dual
  6  union
  7  select '1234511'
  8  from dual
  9  union
 10  select '23451'
 11  from dual
 12  union
 13  select '345'
 14  from dual
 15* order by 1
SQL> /

'ENGLISHLETTERS
---------------
12345
1234511
23451
345
English letters


Secondly....the values u mentioned is not considered as a number ( eg '12345' ) since it's in quote, it's a char. Here is some examples of different combinations.


SQL> select 'English letters'
from   dual
union
select '12345'
from dual
union
select '1234511'
from dual
union
select '23451'
from dual
union
select '345'
from dual
order by 1 desc

SQL> /

'ENGLISHLETTERS
---------------
English letters
345
23451
1234511
12345


SQL> ed
Wrote file afiedt.buf

  1  select 9999999 English_letters
  2  from   dual
  3  union
  4  select 12345
  5  from dual
  6  union
  7  select 1234511
  8  from dual
  9  union
 10  select 23451
 11  from dual
 12  union
 13  select 345
 14  from dual
 15* order by 1

SQL> /

ENGLISH_LETTERS
---------------
            345
          12345
          23451
        1234511
        9999999


SQL> ed
Wrote file afiedt.buf

  1  select 9999999 English_letters
  2  from   dual
  3  union
  4  select 12345
  5  from dual
  6  union
  7  select 1234511
  8  from dual
  9  union
 10  select 23451
 11  from dual
 12  union
 13  select 345
 14  from dual
 15* order by 1 desc
SQL> /

ENGLISH_LETTERS
---------------
        9999999
        1234511
          23451
          12345
            345

Please clarify the requirement...let me know, if you have further questions.

Brams
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

VadimJAuthor Commented:
Rbramhane,

I've run your query, but I've got the different response!  Letters go ahead of numbers:

SQL> select 'English letters'
  2  from   dual
  3  union
  4  select '12345'
  5  from dual
  6  union
  7  select '1234511'
  8  from dual
  9  union
 10  select '23451'
 11  from dual
 12  union
 13  select '345'
 14  from dual
 15  order by 1
 16  /

'ENGLISHLETTERS
---------------
English letters
12345
1234511
23451
345


Why?!?
syakobsonCommented:
ORDER BY sort order is based on NLS_SORT parameter. By default NLS_SORT is set to binary, which means ORDER BY will use collating sequence of your character set. This way characters with smaller code will appear first. I am not familiar with all the character sets, but letters in windows default character set WE8ISO8859P1 and Unix default character set US7ASCII have higher codes than digits:

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_SORT');

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_CHARACTERSET                                                 WE8ISO8859P1
NLS_SORT                                                         BINARY

SQL> select 'B' from dual
  2  union all
  3  select 'A' from dual
  4  union all
  5  select 'a' from dual
  6  union all
  7  select 'b' from dual
  8  union all
  9  select '1' from dual
 10  order by 1;

'
-
1
A
B
a
b

SQL>
The fact you see letters first means you either using some exotic character set or, most likely, NLS_SORT is not set to BINARY. In most languages numbers follow letters rather that preceed them like in ASCII codes. For example, setting NLS_SORT to LATIN will treat lower case and upper case letters as having same weight but also will put digits after letters:

SQL> alter session set nls_sort='latin';

Session altered.

SQL> select 'B' from dual
  2  union all
  3  select 'A' from dual
  4  union all
  5  select 'a' from dual
  6  union all
  7  select 'b' from dual
  8  union all
  9  select '1' from dual
 10  order by 1;

'
-
A
a
B
b
1

As you can see, changing to LATIN put digits after letters. So check NLS_SORT setting using:

select * from v$nls_parameters where parameter='NLS_SORT';

If it is not set to binary, either issue:

alter session set nls_sort='binary';

or check for NLS_SORT entry in windows registry on your client (or NLS_SORT environment variable if client is Unix box). If it is not set there, then check init.ora file on your database server.

Solomon Yakobson.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VadimJAuthor Commented:
Thanks, Solomon, it helps!!

NLS_SORT was set to RUSSIAN, so numbers followed letters. Why Oracle corp. thinks that russian sort letters before numbers?!? Microsoft Windows (russian edition) sorts filenames vice versa.
Thank you again!

Vadim
syakobsonCommented:
Vadim, Microsoft Windows russian edition as well as any other language edition sorts filenames with numbers preceeding letters since it is using BINARY sort - sort based on chracter set codes (collating sequence). Besides collating sequence sort there is lexical sort which is language specific. Check Note:29301.1 on MetaLink. Just in case you have no access to MetaLink, I will post it here:

THE ORDERING WITHIN SORTING SEQUENCES IN ORACLE
-----------------------------------------------

ORACLE allows you to define the order in which characters and numbers
are returned, when an ORDER BY clause is used, by setting the parameter
NLS_SORT.  GROUP BY uses BINARY ordering, except where ORDER BY is
specified.

NLS_SORT can be set:

    1. as an environment variable (from release 7.1.3 onwards).

    2. as a parameter in the initialization file.

    3. using an alter session command.

There are two main groups of encoding schemes: on Unix machines, the scheme
is usually ASCII, whereas the majority of mainframes use EBCDIC.


ASCII sorts as follows:

    numbers
    upper case characters (A-Z)
    lower case characters (a-z)


EBCDIC sorts in the reverse order:

    lower case characters (a-z)
    upper case characters (A-Z)
    numbers


By default, NLS_SORT is defined by the value of NLS_LANGUAGE (the first
component of the NLS_LANG setting). When NLS_LANGUAGE is set to AMERICAN,
NLS_SORT defaults to BINARY.  This orders by the ascending byte value of
the character in the encoding scheme, in the order illustrated above.

In addition, ORACLE provides sorting sequences which cater to the convention
in each country. This is a lexical sorting and the sequence is named the
same as the language. Extended sequences are also available for some
countries, which allow language-specific characters to be sorted correctly,
such as the German 'sharp S', or the dipthongs (letter represented by two
joined characters) used in many eastern European languages. These sequences
are called by the language name prefixed by X, eg: XGERMAN.

While the order of the individual characters depends on the individual
language and its conventions, the case ordering and position of numbers
is as follows:

- Characters sorted alphabetically, but case-insensitive, followed by
  numbers:

    eg: AaBbCc ... 0123456789

    Arabic, Czech, Danish, Dutch, Finnish, French, German, German_DIN,
    Greek, Hebrew, Hungarian, Italian, Latin, Lithuanian, Norwegian,
    Russian, Slovak, Spanish, Swedish, Turkish, West_European


- Numbers followed by characters sorted alphabetically, but case-insensitive:

    eg: 0123456789 AaBbCcDd ...

    Icelandic, Polish

As you can see, it confirms NLS_SORT=Russian is sorting letters before numbers. Even though this note does not refer to any ISO or other standards, the fact Icelandic and Polish sorts put numbers in front of letters while other languages put letters in front of numbers indirectly suggests Oracle was using some sort of standards.

Solomon Yakobson.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.