Printable View
Hi,
I've got a problem when I sort the results. Look this.
The original set was:
cc
aa
bb
11
33
22
And i want to sort this with the ascending ascii criteria:
11
22
33
aa
bb
cc
But Oracle return, doing an 'order by' this set:
aa
bb
cc
11
22
33
How can I solve this problem? Is there any way to
specify the ascii criteria in the 'order by' clause?
That is, i want something like:
'order by ascii(column)'
but not for only the first character; i want this for all
the characters of the field
U can use DUMP for sort with the ascending ascii criteria:
...
order by substr(dump(field_name), insrt(dump(field_name),':') + 2) asc
[Edited by Shestakov on 05-09-2002 at 03:51 PM]
SQL> select table_name, substr(dump(table_name), instr(dump(table_name),':') + 2) from all_tables
where rownum < 10;
------------------------------------------------------------------------------------------------------------------------------------------------------
IND$ 73,78,68,36
FILE$ 70,73,76,69,36
UNDO$ 85,78,68,79,36
CLU$ 67,76,85,36
BOOTSTRAP$ 66,79,79,84,83,84,82,65,80,36
ICOL$ 73,67,79,76,36
It doesn't work. The sorting must be applied to all the
column, not only for the 2 first characters
It works.Quote:
Originally posted by id
It doesn't work. The sorting must be applied to all the
column, not only for the 2 first characters
SQL> create table train.test_tbl (a varchar2(10), b varchar2(10));
insert into train.test_tbl values('aaa', 'aa111');
insert into train.test_tbl values('bbb', 'aa222');
insert into train.test_tbl values('ccc', 'aa333');
insert into train.test_tbl values('11a', 'aa444');
insert into train.test_tbl values('21a', 'aa4aa');
insert into train.test_tbl values('1a2', 'aa4bb');
commit;
select a, substr(dump(a), instr(dump(a),':') + 2) c from train.test_tbl
order by substr(dump(a), instr(dump(a),':') + 2);
A C
-------------------- ------------------------------
11a 49,49,97
1a2 49,97,50
21a 50,49,97
aaa 97,97,97
bbb 98,98,98
ccc 99,99,99
6 rows selected.
SQL>
select b, substr(dump(b), instr(dump(b),':') + 2) c from train.test_tbl
order by substr(dump(b), instr(dump(b),':') + 2);
B C
-------------------- ------------------------------
aa111 97,97,49,49,49
aa222 97,97,50,50,50
aa333 97,97,51,51,51
aa444 97,97,52,52,52
aa4aa 97,97,52,97,97
aa4bb 97,97,52,98,98
6 rows selected.
Well, it works only if the characters set is into 0 to 99.
It doesn't work with chars greater than 99. Try to sort this:
raul
ana
bis
The result must be:
ana
bis
raul
But the result is:
raul
ana
bis
'cause raul starts with a character greater than 99
Hi,
Honestly, I don't understand the problem.
When I run the query I get the sorted output as 'u' expect
i.,e
11
22
33
aa
bb
etc.,
Can u post which version of Oracle u r using? I am using 8.1.7
I tried the same with the data 'raul, ana, bis' as well... and getting it right as u expect.
What is that u r trying to do?
Do correct me if I am wrong!
Cheers
Nandu
Hi,
Maybe the problem is my poor english :-/
Well, I'll try to explain this with a practical example.
I've got this list of unsorted items:
select a from test_tbl
11a
ana
1a2
raul
bis
21a
aaa
bbb
ccc
And when I sort that set, I get the next result:
select a from test_tbl order by a
aaa
ana
bbb
bis
ccc
raul
1a2
11a
21a
Obviusly, this result is not sorted in ascii criteria ('cause
'1a2' code is less than 'aaa'). I don't know how I can sort
in the ascending ascii order criteria. The result that I expect
would be:
11a
1a2
21a
aaa
ana
bbb
bis
ccc
raul
And with the solution that you propose to me I get this result:
select a from test_tbl order by substr(dump(a), instr(dump(a),':') + 2);
raul
11a
1a2
21a
ana
aaa
bis
bbb
ccc
Ooops, 'raul' is in the first position! This occurs 'cause 'raul'
starts with a character greater than 100, returned by the
dump instruction. And then, 'raul' -> '100,xx,xx' is smaller than '11a' -> '50,xx,xx'
Hope that we can understand the problem
U just have to read little bit more about DUMP()
SQL> insert into train.test_tbl values ('rrrr','11r11');
1 row created.
SQL> insert into train.test_tbl values ('123rr', 'r1r1r1');
1 row created.
select b, substr(dump(b,16), instr(dump(b,16),':') + 2) c from train.test_tbl
order by substr(dump(b,16), instr(dump(b,16),':') + 2);
B C
------------------------------ --------------------
11r11 31,31,72,31,31
aa111 61,61,31,31,31
aa222 61,61,32,32,32
aa333 61,61,33,33,33
aa444 61,61,34,34,34
aa4aa 61,61,34,61,61
aa4bb 61,61,34,62,62
r1r1r1 72,31,72,31,72,31
select a, substr(dump(a,16), instr(dump(a,16),':') + 2) c from train.test_tbl
order by substr(dump(a,16), instr(dump(a,16),':') + 2);
A C
-------------------- --------------------
11a 31,31,61
123rr 31,32,33,72,72
1a2 31,61,32
21a 32,31,61
aaa 61,61,61
bbb 62,62,62
ccc 63,63,63
rrrr 72,72,72,72
I hope it may be help u.
I tried to do this, but it doen't work.
When we pass '16' in the dump instruction, it
returns the hexadecimal code representation.
But we're in the same problem when we compare
codes with alpha digits. That is:
'a' -> 61 //Hexa
'o' -> 6f //Hexa
When i try to sort these 2 strings i get the same
incorrect result that i described:
'o'
'a'
FOR ID
Hi,
Which version of Oracle are you using...
I am getting the result u want from simple order by clause.
SQL>select * from test_db
2 order by 1;
A
----------
11a
1a2
21a
aaa
ana
bbb
bis
ccc
raul
9 rows selected.
Take Care.
Looks like EBCDIC sorting order.
Try "ALTER SESSION SET NLS_SORT=BINARY".
Ales
Hi,
It works, not for the type that you specify; it
works for (we're from Spain):
NLS_SORT = eec_euro
Thanks a lot!!!!
Try this
select *
from MyTable
order by NLSSORT(MySortColumn,'NLS_SORT = BINARY')
Not sure it will give the good result.
just try it.
Regards
Gert