Professional Web Applications Themes

Sort order - IBM DB2

Hi, I've a table with some rows. With 'select * from tab order by sp1' I've got the following: sp1 --- a1 a2 aa ab Is it possible to change the sort order to get this: sp1 --- aa ab a1 a2 Regards Michael...

  1. #1

    Default Sort order

    Hi,

    I've a table with some rows.

    With 'select * from tab order by sp1' I've got the following:

    sp1
    ---
    a1
    a2
    aa
    ab

    Is it possible to change the sort order to get this:

    sp1
    ---
    aa
    ab
    a1
    a2


    Regards

    Michael
    hobbit Guest

  2. #2

    Default Re: Sort order

    You can create a database with User Defined Collation Sequence. In DB2 v8,
    we have a sample program in sqllib/samples/c/dbcreate.c. The sample program
    is in sqllib/samples/c/ebcdicdb.c.

    Sherman

    "hobbit" <michael.spodenweb.de> wrote in message
    news:7a88ec99.0307070737.5299cf25posting.google.c om...
    > Hi,
    >
    > I've a table with some rows.
    >
    > With 'select * from tab order by sp1' I've got the following:
    >
    > sp1
    > ---
    > a1
    > a2
    > aa
    > ab
    >
    > Is it possible to change the sort order to get this:
    >
    > sp1
    > ---
    > aa
    > ab
    > a1
    > a2
    >
    >
    > Regards
    >
    > Michael

    Sherman Lau Guest

  3. #3

    Default Re: Sort order

    Collating sequence is a DB attribute that you can't modify.
    See Create Database ... COLLATE USING ...

    The following is dangerous because you rely on cs/cp ordering values
    I can't garantee it will work in all environments.
    You'd also have to handle all the non a-z, A-Z.

    The idea is to push the numbers to higher fake-code-points.

    with taba(col1) as
    (values 'a2', 'ab', 'aa', 'a1', 'ac', 'ad', 'ae', 'a3', 'a4', 'a5', 'a0',
    'b0', 'b1', 'bb', '00', '01', '0c', '0a', '1b', 'Z9', 'A0', 'z9', 'z0')
    select
    CAST(translate(col1,
    (x'7C'||x'7D'||x'7E'||x'7F'||x'80'||x'81'||x'82'|| x'83'||x'84'||x'85'),
    '0123456789' ) AS CHAR(02) FOR BIT DATA) as cvt,
    col1,
    cast(col1 as char(02) for bit data
    ) from taba order by cvt

    CVT COL1 3
    --------- ---- -------
    x'417C' A0 x'4130'
    x'5A85' Z9 x'5A39'
    x'6161' aa x'6161'
    x'6162' ab x'6162'
    x'6163' ac x'6163'
    x'6164' ad x'6164'
    x'6165' ae x'6165'
    x'617C' a0 x'6130'
    x'617D' a1 x'6131'
    x'617E' a2 x'6132'
    x'617F' a3 x'6133'
    x'6180' a4 x'6134'
    x'6181' a5 x'6135'
    x'6262' bb x'6262'
    x'627C' b0 x'6230'
    x'627D' b1 x'6231'
    x'7A7C' z0 x'7A30'
    x'7A85' z9 x'7A39'
    x'7C61' 0a x'3061'
    x'7C63' 0c x'3063'
    x'7C7C' 00 x'3030'
    x'7C7D' 01 x'3031'
    x'7D62' 1b x'3162'

    23 record(s) selected.


    This statement has been executed against a db2 with level of (db2level)
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08012" with
    level identifier "02030106".
    Informational tokens are "DB2 v8.1.2.169", "s030508", "WR21318", and FixPak
    "2".

    PM


    PM \(pm3iinc-nospam\) Guest

  4. #4

    Default Re: Sort order

    You can also look on the net for ICU*
    or java packages that contain *colla* in their name.

    PM


    PM \(pm3iinc-nospam\) Guest

  5. #5

    Default Re: Sort order

    First, you need to know the default collating sequence.
    This is the default collating sequence in Japanese environment.
    (I don't know codepage number)
    WITH sample_data(col1) AS (
    VALUES 'a2', 'ab', 'aa', 'a1', 'ac', 'ad', 'ae', 'a3', 'a4', 'a5', 'a0',
    'b0', 'b1', 'bb', '00', '01', '0c', '0a', '1b', 'Z9', 'A0', 'z9', 'z0'
    )
    SELECT *
    FROM sample_data
    ORDER BY col1
    ;
    ---------------------------------------------------

    COL1
    ----
    00
    01
    0a
    0c
    1b
    a0
    a1
    a2
    a3
    a4
    a5
    aa
    ab
    ac
    ad
    ae
    A0
    b0
    b1
    bb
    z0
    z9
    Z9

    23 record(s) selected.


    Then you can change ordering like this way:
    WITH sample_data(col1) AS (
    VALUES 'a2', 'ab', 'aa', 'a1', 'ac', 'ad', 'ae', 'a3', 'a4', 'a5', 'a0',
    'b0', 'b1', 'bb', '00', '01', '0c', '0a', '1b', 'Z9', 'A0', 'z9', 'z0'
    )
    SELECT *
    FROM sample_data
    ORDER BY
    TRANSLATE
    (col1
    , '0123456789aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsSt TuUvVwWxXyYzZ'
    -- default sequence
    , 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789'
    -- desired sequence
    )
    ;
    ---------------------------------------------------

    COL1
    ----
    A0
    Z9
    aa
    ab
    ac
    ad
    ae
    a0
    a1
    a2
    a3
    a4
    a5
    bb
    b0
    b1
    z0
    z9
    0a
    0c
    00
    01
    1b

    23 record(s) selected
    Tokunaga T. Guest

Similar Threads

  1. PHP Sort order definitions
    By Csaba Gabor in forum PHP Development
    Replies: 0
    Last Post: May 3rd, 11:57 AM
  2. strange sort order
    By Dan Laflamme in forum PERL Beginners
    Replies: 10
    Last Post: January 15th, 05:12 PM
  3. Natural order sort
    By Alan Davies in forum Ruby
    Replies: 4
    Last Post: September 15th, 06:02 PM
  4. no sort order for clients?
    By Lorenz in forum FileMaker
    Replies: 1
    Last Post: July 28th, 02:08 PM
  5. sql SORT order not working on numbers?
    By Alpay Eno in forum ASP Database
    Replies: 2
    Last Post: July 10th, 03:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139