Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    7

    Unanswered: Query Regarding Union

    Hi 2 All,

    I am getting a prblm while I am joining two queries using Union.
    My Query will be like this,

    SELECT * FROM CMTRAVELINSTRUCTION
    ORDER BY CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID DESC
    UNION
    SELECT * FROM CMTRAVELINSTRUCTION
    ORDER BY CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID DESC;

    I have to get the result ordered by travelinstructionid descending.

    Note that I am selecting same fields from each query.

    While I am using above query, i am getting an error like
    'sql command not properly ended'

    Hope Anyone can help me out in this context.

    Regards
    Suresh Midde

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Your statement does not make sense.
    UNION will remove duplicates from the total result, as you select the identical result two times, the result will be exactly the same as without the union - so it will simply return all rows from CMTRAVELINSTRUCTION. If you want to keep duplicates you need to use UNION ALL.

    Having said this, your error is caused by the fact that an ORDER BY can only be applied to the complete result of a UNION, not to single parts of it.

    And you probably need to reference the sort column using the column index not its name (not sure about that though), e.g:
    Code:
    SELECT * FROM first_table
    UNION
    SELECT * FROM second_table
    ORDER BY 1 DESC;

  3. #3
    Join Date
    Sep 2007
    Posts
    7
    Hi Shammat,

    What does 1 refer to in "order by 1 desc"?

    Is that the first column that is going to be retrieved ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, it is the first column.

    If your query was
    Code:
    SELECT deptno, empno, ename, job, sal
    FROM emp
    ORDER BY 1, 5 DESC
    it would be as if you've put it as
    Code:
    ORDER BY deptno, sal DESC

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just as an aside, for an adhoc query something like "select *" is fine, but never use it for any production code. If you are going to pursue a career in oracle, get into the habit of specifying the columns.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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