Oracle Union with order by clause issue

Question

Actual query looks something like this:

select to_char(first_name||'('||substr(last_name,0,1)||')') 
from employees 
order by first_name
union
select to_char('There are total '
||count(job_id)||' '||lower(job_id)||'s') 
from employees
group by job_id 
order by count(job_id),job_id;

individually both queries work but when I add Union to combine the result, I get following errorm in sql developer:

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 2 Column: 1

I want to show all the records from the first query then all the records from the second query one after another, each set sorted in the order given above.

Answer 1

The underlying obstacle is that a SQL dataset is inherently UNordered. As soon as you UNION two datasets you lose any guarantee of ordering previously present.

You can often get away with the following structure, but it is STILL not guaranteed...

SELECT
    *
FROM
(
    (
        select to_char(first_name||'('||substr(last_name,0,1)||')') 
        from employees 
        order by first_name
    ) 
        Individuals

    UNION ALL

    (
        select to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') 
        from employees
        group by job_id 
        order by count(job_id),job_id
    )
        Totals
)
    Combined
;

In practice you Often get what you want with this structure.

The brackets ensure the ordering is done before the UNION ALL and the database engine PROBABLY won't screw with the ordering.

But it MIGHT. The only way to Guarantee the order of the results is to put an ORDER BY on the outer query. Something like the following normally works fairly well...

SELECT
    rowdata
FROM
(
    (
        select 1 AS myset, rownum AS myrow, to_char(first_name||'('||substr(last_name,0,1)||')') AS rowdata
        from employees 
        order by first_name
    ) 
        Individuals

    UNION ALL

    (
        select 2 AS myset, rownum AS myrow, to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') 
        from employees
        group by job_id 
        order by count(job_id),job_id
    )
        Totals
)
    Combined
ORDER BY
    myset, myrow
;

I'm on my phone, so there may be typos, but the sentiment is there...

Use ROWNUM or ROW_NUMBER() to generate an extra field in your data sets. Then union them. Then order by your new field(s).

Answer 2

it should be work

    select to_char(first_name||'('||substr(last_name,0,1)||')') 
        from (select first_name, last_name from employees order by first_name)
    union
    select to_char('There are total '||cnt||' '||lower(job_id)||'s')
        from (select count(job_id) cnt, job_id from employees group by job_id 
order by count(job_id), job_id)

Answer 3

The way to do that is typically by supplying a column that identifies the "union group" in which a row should appear:

select name 
from (
  select first_name||'('||substr(last_name,0,1)||')' as name, 
         1 as sort_order, 
         0 as counter, 
         0 as job_id
  from employees 
  union all 
  select 'There are total '||count(job_id)||' '||lower(job_id)||'s', 
         2, 
         count(job_id), 
         job_id
  from employees
  group by job_id 
) t
order by sort_order, counter, job_id, name;

As you also want to sort the rows inside the second part by a different column, you need to create dummy columns for that, so that you can apply the correct order by on the overall result.

I also remove the useless to_char() calls from your query.

If job_id is in fact a varchar column (which the lower(job_id) seems to indicate), then you need to replace 0 as job_id with some character constant. It doesn't really matter which value it is.

Online example: http://rextester.com/ZUPQ98121