Originally posted by dba_akram
I dont think it is a good practice to have columns selected more than once ....
Here it is not a matter of good practice, it is the only way for you to use order by in your union-ed example. If you want to use ORDER BY with UNION, the ordered expression must be listed in all unioned subqueries. In your case you must include STATUS_CHANGE_DATE in both your queries and then order by that column. BTW, you don't need to do
Code:
ORDER BY to_date(to_char(tsc.status_change_date, 'Mon DD, YYYY'), 'Mon DD, YYYY')
when you can simply use
Code:
ORDER BY status_change_date
...... as well as to use position number for ordering the result.
I agree, but sometimes this is the only way to be able to use ORDER BY with UNION. In earlier releases that was the only option. Starting with 8i (I think) you can also use ORDER BY column_alias, where column_alias must be the same in all union-ed queries.

So depending on your release, your solution would be:
Code:
SELECT
  tsc.status_change_date AS status_change_date,
  .....
FROM
  .....
UNION
SELECT
  tsc.status_change_date AS status_change_date,
  .....
FROM
  .....
ORDER BY status_change_date;
or
Code:
SELECT
  tsc.status_change_date AS status_change_date,
  .....
FROM
  .....
UNION
SELECT
  tsc.status_change_date AS status_change_date,
  .....
FROM
  .....
ORDER BY 1;
P.S. Note that there is no need to use DISTINCT in queries with UNION - the UNION operator by itself will return you only distinct rows.