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

grofaty · #1 (**permalink**) 01-08-08, 08:49

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

ARWinner · #2 (**permalink**) 01-08-08, 11:34

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

stolze · #3 (**permalink**) 01-08-08, 11:35

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech