Order by restriction
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Order by restriction

  1. #1
    Join Date
    May 2001
    Posts
    57

    Angry

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    First :
    take this expression in the attribute list
    Second :
    use
    order by

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

    Orca

  3. #3
    Join Date
    May 2001
    Posts
    57
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

  7. #7
    Join Date
    Aug 2000
    Posts
    462
    BTW, what does it get you to use "distinct", since "UNION" eliminates ALL duplicates from the result set anyway?
    Oracle DBA and Developer

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    May 2001
    Posts
    57
    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.

  10. #10
    Join Date
    Jan 2008
    Location
    Hampshire, UK
    Posts
    7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width