| Sorting a table in ascending ascii criteria [message #1566] |
Mon, 13 May 2002 10:40  |
Alex
Messages: 190
Registered: August 1999
|
Senior Member |
|
|
Hi,
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
I thought a solution, but it doesn't work for chars
greater than 99:
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
|
|
|
|
| Re: Sorting a table in ascending ascii criteria [message #1569 is a reply to message #1566] |
Tue, 14 May 2002 00:10  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Understand, yes; reproduce, no.
I created a table:
SQL> select * from temp;
COL_1
----------
aab
11a
bbb
21a
6aa
def
Doing a query:
SQL> select col_1 from temp order by col_1;
COL_1
----------
11a
21a
6aa
aab
bbb
def
Gives exactly the answer you'd expect.
Changing the type of the column to CHAR(10) from VARCHAR2(10) makes no change.
Questions:
1/What do you get if you ORDER BY SUBSTR(a,1,1)?
2/What is the structure of your table?
3/Are there any leading or trailing spaces attached to the strings?
4/Does a Dump of the strings show any control chrs in the strings?
|
|
|
|
| Re: Sorting a table in ascending ascii criteria [message #1572 is a reply to message #1569] |
Tue, 14 May 2002 06:18 |
Alex
Messages: 190
Registered: August 1999
|
Senior Member |
|
|
Well, I don't know where's the problem, but it
doesn't work.
1.- ORDER BY SUBSTR(a,1,1) gives me the incorrect
result, in the same way that i described
2.- The column is a varchar(40)
3, 4.- The string returned by dump is correct and
no special treatment is needed, also for the column
content (no trailing or leading spaces or characters
into dump or column)
Maybe the problem is the character set -it's not
US-ASCII 7bit-, but the result must be the same!
|
|
|
|
| Re: Sorting a table in ascending ascii criteria [message #1587 is a reply to message #1569] |
Wed, 15 May 2002 06:04 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
I think you've got it there. It'll be the chr set.
In the set you are using I suspect that the numbers have a higher representational value than the letters.
Try doing this:
SELECT a,dump(a) FROM table ORDER BY a;
I'll bet that the first character of the dump column is in increasing numeric order.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
