sorting in oracle?

If I create a table:

CREATE TABLE FOO
(
BAR VARCHAR(5)
);

insert into foo values ('a');
insert into foo values ('b');
insert into foo values ('c');
insert into foo values ('A');
insert into foo values ('B');
insert into foo values ('C');

and run this select statement:

select bar from foo order by bar;

I get back:

BAR
-----
A
B
C
a
b
c

Why don't I get back...

BAR
-----
A
a
B
b
C
c

-Thx

On Jul 11, 2:59 pm, kilik3...@gmail.com wrote:
> If I create a table:
>
> CREATE TABLE FOO
> (
> BAR VARCHAR(5)
> );
>
> insert into foo values ('a');
> insert into foo values ('b');
> insert into foo values ('c');
> insert into foo values ('A');
> insert into foo values ('B');
> insert into foo values ('C');
>
> and run this select statement:
>
> select bar from foo order by bar;
>
> I get back:
>
> BAR
> -----
> A
> B
> C
> a
> b
> c
>
> Why don't I get back...
>
> BAR
> -----
> A
> a
> B
> b
> C
> c
>
> -Thx


Answers are usually version dependent. 5 digits and 3 dots.
The answer to your question is Oracle by default has NLS_SORT=binary,
which results in everything being ordered in the order of the ASCII
characterset.l
Case insensitive sorting is available in 10g and higher.

--
Sybrand Bakker
Senior Oracle DBA

On Jul 11, 7:59 am, kilik3...@gmail.com wrote:
> If I create a table:
>
> CREATE TABLE FOO
> (
> BAR VARCHAR(5)
> );
>
> insert into foo values ('a');
> insert into foo values ('b');
> insert into foo values ('c');
> insert into foo values ('A');
> insert into foo values ('B');
> insert into foo values ('C');
>
> and run this select statement:
>
> select bar from foo order by bar;
>
> I get back:
>
> BAR
> -----
> A
> B
> C
> a
> b
> c
>
> Why don't I get back...
>
> BAR
> -----
> A
> a
> B
> b
> C
> c
>
> -Thx

Consult a table of ASCII characters and you'll realise the capital
letters have lower ASCII values than the lower case letters. The sort
is based upon the ASCII code. A quick search on Google would have
provided numerous links to explain this.


David Fitzjarrell

On Wed, 11 Jul 2007 05:59:54 -0700, kilik3000 wrote:

> Why don't I get back...

Because Oracle hates you.

--
http://www.mladen-gogala.com