Order by on single column

Question

I have Informix code:

select data as DATA from (
(select distinct('Date|Message_Type|Total') as data,0 as sort from dual)
union
(select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data,1 as sort from weekly_report_1)
                 )order by sort)

Output:

data1
Date|Message_Type|Total
dt: 02/10/2015|1440|0.00
dt: 02/10/2015|Total|-26290965.84
dt: 02/11/2015|1240|-23550538.87
dt: 02/11/2015|1440|0.00
dt: 02/11/2015|Total|-23550538.87
dt: 02/10/2015|1240|-26290965.84
dt: 02/12/2015|1240|-24181913.23
dt: 02/14/2015|Total|0.00
dt: 02/12/2015|1440|0.00
dt: 02/12/2015|Total|-24181913.23
dt: 02/13/2015|1240|-24611999.80
dt: 02/13/2015|1440|0.00
dt: 02/13/2015|Total|-24611999.80
dt: 02/14/2015|1240|0.00
dt: 02/14/2015|1440|0.00

When I am trying to do the following:

select * from(
select data as DATA1 from (
(select distinct('Date|Message_Type|Total') as data,0 as sort from dual)
union
(select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data,1 as sort from weekly_report_1)
                 )order by sort) order by DATA1;

I am getting:

Error: General error.Syntax error (State:37000, Native Code: FFFFD4CC)

I need to sort the table.

Selecting 1 as `sort` and then ordering by `sort` is guaranteed not to sort. Whatever your data is. — Mike Nakis, Feb 16 '15 at 16:51
Not tested, but as a quick punt (assuming you want to sort by date ... `select distinct('Date|Message_Type|Total') as data from dual union (select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data from weekly_report_1 order by day)` — Jeremy Gosling, Feb 16 '15 at 17:34
Actually, trying that out on an Oracle database, the problem may be that you can't specify an order by on the subselect in a union. However, [this](http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union) article has a work round ... `select distinct('Date|Message_Type|Total') as data from dual union select * from ( select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data from weekly_report_1 order by day );` — Jeremy Gosling, Feb 16 '15 at 17:50

score 2 · Answer 1 · answered Feb 16 '15 at 18:54

You can only have one ORDER BY clause. You don't need the second level of sub-query.

SELECT data AS Data1 
  FROM (SELECT DISTINCT('Date|Message_Type|Total') AS Data,
               0 AS Sort
          FROM dual
        UNION
        SELECT 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) AS Data,
               1 AS Sort
          FROM weekly_report_1
       ) AS AnyName
ORDER BY Sort, Data1;

Unless you have a seriously archaic (many years overdue for replacement) version of Informix, you can sort by a column that isn't listed in the select-list. If you do have a sufficiently archaic version of Informix that this doesn't work, you'll need to select the Sort value too. Or you can rely on Date preceding dt in the code set you're using and simply omit the ordering on Sort.

Order by on single column

1 Answers1