Re: ORDER BY clause without sorting needed...

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 10 Nov 1998 21:37:14 GMT
Message-ID: <3648a864.6892174@news.siol.net>

On Tue, 10 Nov 98 14:48:20 +0200, igoryok_at_soft-review.kiev.ua (Igor V. Podolsky) wrote:

>Hi all !
>
>I got a following troubles: I wish that my query result set will be not
>sorted, but I with still have ORDER BY clause in it. Now I'm using 'ORDER
>BY -1' and following query example works fine:
>
>SELECT id, name FROM customer ORDER BY -1
>
>This query returns result set without sorting. (By the way:
>
>SELECT id, name FROM customer ORDER BY 1
>
>returns sorted by 'id' result set). But I got error 'ORA-01785:
>ORDER BY item must be the number of a SELECT-list expression' when I try
>'ORDER BY -1' with UNION queries. Following statement causes error:
>
>SELECT id, name FROM customer
>UNION
>SELECT 0, ' ' FROM dual
>ORDER BY -1
>
>Is there any another way to prevent query with ORDER BY clause from
>sorting ? Why 'ORDER BY -1' works by different way with the UNION query ?

I didn't even know that 'ORDER BY -1' works on non-union selects and I also don't know why it's behaving differentely with UNION, I just want to warn you that the above union select (with the 'ORDER BY -1' part removed) will allways return sorted result set (ordered by id, name) because of the nature of the UNION clause. If you want to avoid sorting and allow duplicate rows in the result set you must use UNION ALL operator.

Although it is not quite clear to me what you want to achive with the "meaningless" ORDER BY clause, I sugest you include a dummy constant in both your UNIONed selects and order the result set by this constant, eg:

SELECT null, id, name FROM customer
UNION ALL
SELECT null, 0, ' ' FROM dual
ORDER BY 1
>P.S. Duplicate answers (if any :( ) to my e-mail please...
>
>--
>Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)
>
>Igor V. Podolsky (igoryok_at_soft-review.kiev.ua)

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Nov 10 1998 - 15:37:14 CST