Order by restriction

[dba_akram]

Hi All,
am executing the following query with the union clause in my sqlplus or even pl/sql block.
SELECT distinct t.tdd_id,
ts.abbreviation,
po.last_name||', '||po.first_name,
c.customer_name,
to_char(tsc.status_change_date, 'Mon DD, YYYY')
FROM tdd t,
tdd_status ts,
person p,
person po,
telecom_authority ta,
telecom_authority ta1,
customer c,
tdd_status_change tsc,
servicing_type st,
service s,
tdd_person tp,
product prod,
role r
WHERE t.tdd_id = tsc.tdd_id
AND tsc.tdd_status_id = ts.tdd_status_id
AND c.customer_id = t.customer_id
AND t.servicing_type_id = st.servicing_type_id
AND tsc.tdd_status_change_id = (select max(tdd_status_change_id)
from tdd_status_change where tdd_id = t.tdd_id)
AND po.person_id = t.tdd_owner
AND p.person_id = p_person_id
AND tp.person_id = p.person_id
AND t.tdd_id = tp.tdd_id
AND st.product_id = prod.product_id
AND rownum < 502
UNION
SELECT distinct t.tdd_id,
ts.abbreviation,
po.last_name||', '||po.first_name,
c.customer_name,
replace(t.tdd_description,'
',' '),
t.tdd_number,
to_char(tsc.status_change_date, 'Mon DD, YYYY')
FROM tdd t,
tdd_status ts,
person p,
person po,
telecom_authority ta,
telecom_authority ta1,
customer c,
tdd_status_change tsc,
servicing_type st,
service s,
tdd_person tp,
product prod,
role r
WHERE t.tdd_id = tsc.tdd_id
AND c.customer_id = t.customer_id
AND t.servicing_type_id = st.servicing_type_id
AND tsc.tdd_status_change_id = (select max(tdd_status_change_id)
from tdd_status_change where tdd_id = t.tdd_id)
AND po.person_id = t.tdd_owner
AND p.person_id = p_person_id
AND tp.person_id = p.person_id
AND t.tdd_id = tp.tdd_id
AND st.product_id = prod.product_id
AND rownum < 502
ORDER BY to_date(to_char(tsc.status_change_date, 'Mon DD, YYYY'), 'Mon DD, YYYY');
I get an error message like this
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

If I execute the query separately without the union clause just with the order by it works like the way I want.
Can anyone of you please let me know the way to overcome this situation. Iam expecting the moderators to show me the way to get out of it.
Thanks in advance.

[Orca777]

First :
take this expression in the attribute list
Second :
use
order by

order by 1 -- if it's the first in attribute list.

Orca

[dba_akram]

I dont think it is a good practice to have columns selected more than once as well as to use position number for ordering the result. If there is any change in the program unit, the developer has to modify the order by position number at all locations of order by clause. Moderators please advice on this.

[Orca777]

Originally posted by dba_akram
I dont think it is a good practice to have columns selected more than once as well as to use position number for ordering the result. If there is any change in the program unit, the developer has to modify the order by position number at all locations of order by clause. Moderators please advice on this.

ok!
Second variant:

put the SQL in a SUBSQL ordering the named coloumn outside

SELECT column1, column2, ... from ( SELECT ....
)
ORDER BY columnx

[jmodic]

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

ORDER BY to_date(to_char(tsc.status_change_date, 'Mon DD, YYYY'), 'Mon DD, YYYY')

when you can simply use

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:

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

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.

[tamilselvan]

Use INLINE view to solve your problem in which rows are sorted on the date.
Example:
Table TEST
columns ID NUmber , Name Varchar2(30), LOGDATE Date.

Similar to your query:

Select a.id, a.name, a.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) a
union
Select b.id, b.name, b.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) b
;


Use NVL on LOGDATE if it contains NULL values.

[kmesser]

BTW, what does it get you to use "distinct", since "UNION" eliminates ALL duplicates from the result set anyway?

[jmodic]

Originally posted by tamilselvan
Select a.id, a.name, a.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) a
union
Select b.id, b.name, b.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) b
;

The result from the above query will not be sorted by logdate. It will be sorted by ID first, then by NAME and lastly by TDATE.

If you want to sort by LOGDATE, but do not LOGDATE to be includet into the resultset, then you should use:

select id, name, tdate 
from
(select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table1
union
select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table2
)
order by logdate;

[dba_akram]

Thanks to all. I should kick my ass to have asked this question. Because distinct was my main catch in it and as you guys made me realise the silly thing that UNION itself eliminates duplicates. thanks all.

[FalconRime]

There is a quick summary of the different behaviour between Oracle and SQL Server with ORDER BY elements at http://www.sqlexpert.co.uk/2007/11/o...ents-with.html