Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write SQL with order by including union all syntax?

    Hi,
    Table admin.tab has column which looks like this:
    Code:
    aaa
    ccc
    bbb
    I need to order by this column and at the bottom add special character '26'. I need this special character because I need to export this data to TXT file and some third party application imports data to its application.

    So the result should be:
    Code:
    aaa
    bbb
    ccc
    'special character'
    I wrote the following commands:
    Code:
    create table admin.tab (col1 char(10));
    insert into admin.tab values ('aaa');
    insert into admin.tab values ('ccc');
    insert into admin.tab values ('bbb');
    
    select col1 from admin.tab
    union all
    select chr(26) from sysibm.sysdummy1
    order by 1;
    But output puts special character at the top instead at the bottom:
    Code:
    'special character'
    aaa
    bbb
    ccc
    So how to order data and put the special character at the bottom?

    My system: DB2 ESE v8 fp11 on Linux
    Thanks,
    Grofaty
    Last edited by grofaty; 01-08-08 at 09:56.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try:

    with t1 (col1,col2) as
    select col1,col1 from admin.tab
    union all
    select chr(26),chr(255) from sysibm.sysdummy1
    ) select col1 from t1 order by 2;

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would do this:

    Code:
    SELECT coll
    FROM   ( SELECT coll, 1
             FROM   admin.tab
             UNION ALL
             VALUES ( CHR(26), 2 ) ) AS t ( coll, order )
    ORDER BY order, coll
    The idea is to add a temporary column where the values in that column are 1 for all rows retrieved from ADMIN.TAB and 2 for the row with the special character. Then you wrap a SELECT around that to (a) order by the temp column, and (b) filter-out the temp column again.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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