Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Order by Question

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:

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

  4. #4
    Join Date
    Jul 2009
    Posts
    150

    Unhappy

    Quote Originally Posted by tonkuma View Post
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Kara wrote:
    Hex-code depends on code-set and how I understood result will be the same:
    I see.

    The resuirement is...
    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.

  6. #6
    Join Date
    Nov 2008
    Posts
    48
    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

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Not HEX() but ASCII() function

    Quote Originally Posted by tonkuma View Post
    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:

    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:

    G01 G02
    aa bb
    11 dd
    Lenny

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down One problem

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

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

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Something like this

    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
    Last edited by Lenny77; 07-28-10 at 14:02.

  10. #10
    Join Date
    Nov 2008
    Posts
    48
    thanks Lenny

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking

    Quote Originally Posted by phil72 View Post
    thanks Lenny
    No problem.
    You can create UDF function, based on the query which I gave you, and make the ORDER BY using your function.

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •