Order by Question

phil72 · #1 (**permalink**) 07-27-10, 16:23

select S from table(values('0007'), ('@K'), ('DZ')) as s(s) order by s

Result
-----
@K
0007
DZ

Is there a way to sort in ascii order like Javascript, coldfusin, VFP
dsesired Result
----
0007
@K
DZ

n_i · #2 (**permalink**) 07-27-10, 19:09

Works for me:

Code:

D:\>db2 "select S from table(values('0007'), ('@K'), ('DZ')) as s(s) order by s"

S
----
0007
@K
DZ

  3 record(s) selected.

Collating sequence is set when you create the database, and you cannot change it afterwards. You can verify what collating sequence your database is configured with by running

Code:

db2 get db cfg | grep -i collating

tonkuma · #3 (**permalink**) 07-28-10, 00:55

Please try:

select S from table(values('0007'), ('@K'), ('DZ'), ('ab')) as s(s) order by HEX(s)

DB2Plus · #4 (**permalink**) 07-28-10, 06:32

Quote:

Originally Posted by tonkuma

Please try:

select S from table(values('0007'), ('@K'), ('DZ'), ('ab')) as s(s) order by HEX(s)

Hex-code depends on code-set and how I understood result will be the same:
"Order by S" and "order by HEX(s)".

Kara

tonkuma · #5 (**permalink**) 07-28-10, 07:28

Kara wrote:

Quote:

Hex-code depends on code-set and how I understood result will be the same:

I see.

The resuirement is...

Quote:

Is there a way to sort in ascii order like Javascript, coldfusin

I thought ASCII function, like.
ORDER BY ASCII(s)

But, it returns only the ascii code value of the leftmost character.
So, I have no idea now.

phil72 · #6 (**permalink**) 07-28-10, 08:55

select S from table(values('0007'), ('@K'), ('DZ'), ('ab')) as s(s) order by HEX(s)
seems to work fine as Hex code is ordered like ASCII code of these characters
chr Ascii Hex
+ 43 2B
0 48 30
1 49 31
9 57 39
@ 64 40

A 65 41
B 66 42
Z 90 5A
Thank you All

Lenny77 · #7 (**permalink**) 07-28-10, 10:20

Quote:

Originally Posted by tonkuma

Kara wrote:

I see.

The resuirement is...

I thought ASCII function, like.
ORDER BY ASCII(s)

But, it returns only the ascii code value of the leftmost character.
So, I have no idea now.

It was a right solution, tonkuma !

Compare:

Code:

select g01, g02
from
(
select 'aa' g01, 'bb' g02 from sysibm.sysdummy1 
union all
select '11' g01, 'dd' g02 from sysibm.sysdummy1 ) aa
order by ascii(g01)

Result is:

Quote:

G01 G02
11 dd
aa bb

And query:

Code:

select g01, g02
from
(
select 'aa' g01, 'bb' g02 from sysibm.sysdummy1 
union all
select '11' g01, 'dd' g02 from sysibm.sysdummy1 ) aa
order by g01

Where result:

Quote:

G01 G02
aa bb
11 dd

Lenny

Lenny77 · #8 (**permalink**) 07-28-10, 11:43

But, at least one problem:
ASCII function takes first symbol of the string, only:

Code:

select ascii('abcd'), ascii('a')
from sysibm.sysdummy1

Lenny

Lenny77 · #9 (**permalink**) 07-28-10, 12:58

Maybe something like this:

Code:

with strings (str) as
(
select 'abef'  from sysibm.sysdummy1 
union all
select 'abcd' from sysibm.sysdummy1
union all
select 'afghee' from sysibm.sysdummy1
) 
,
ascii_strs (str, len, k, ascii_str) as
(
select str, length(str), 0, varchar('', 255)  
from strings
union all
select str, len, k + 1, 
ascii_str || Right(digits( ascii(substr(str, k + 1, 1) )), 3)
from ascii_strs  
where k + 1 <= len
) 
select str 
from ascii_strs a1
where k = (select max(a2.k) from ascii_strs a2 where a1.str = a2.str) 
order by a1.ascii_str

Lenny