CODEWITHSUNDEEP
sqloracle-database
bd528
bd528

Reputation: 886

Order By one part of a union query

I have a UNION query, and I want just one part of union to be sorted

Query1
UNION
Query2
UNION
Query3

So, I want only to sort Query 2, by the third column. All 3 queries have 3 columns.

Is this possible?

Upvotes: 1

Views: 2539

Answers (3)

mymarkers
mymarkers

Reputation: 452

No, it isn't. And yes it is. You have to sort the entire union with the same order by, but there's nothing stopping you from adding a column or two to the whole union.

So let's say we have tables foo, bar, and baz. Each has columns A, B, and C. Let's use B as the one you want to sort. We'll order B by bar and baz. We'll also force all the results from A to come before all the results from B and put those before C's. We have to do this to make sure B's results are in order.

select a.foo
, a.bar
, a.baz
, 0 as primary_sort
, 0 as secondary_sort
from A
union
select b.foo
, b.bar
, b.baz
, 1 as primary_sort
, row_number() over (order by b.bar, b.baz) as secondary_sort
from B
union
select c.foo
, c.bar
, c.baz
, 2 as primary_sort
, 0 as secondary_sort
from C
order by primary_sort, secondary_sort;

Then if you don't want those sort columns passed along, you can wrap a select statement around it:

select foo, bar, baz from (
-- query from above WITHOUT the order by
) order by primary_sort, secondary_sort;

The key is that primary_sort has to be the same type in each query and so does secondary_sort. The row_number() function lets you add as many columns as you want to the sort and reduces it to a single number. So the over (order by ... ) is what actually declares how you want to order the results from B.

Upvotes: 1

Thomas G
Thomas G

Reputation: 10206

It is not possible in Oracle to ORDER BY an UNIONed nested query. You got the explanation of APC for details.

You can use subquery factoring to overcome this

WITH T2SORTED
AS
(SELECT id FROM T2 ORDER BY id)
SELECT id FROM T1
UNION
SELECT id FROM T2SORTED
UNION
SELECT id FROM T3

SQLfiddle

Upvotes: 2

APC
APC

Reputation: 146209

"Is this possible?"

No. UNION is a set operator, so it will munge the result set of each query into a new result set of distinct records. The output of Query 2 will no longer be identifiable as such.

The exception is if each sub-query produces a unique set of records in their own right, in which case you should use UNION ALL, which doesn't filter for duplicates. Although this leads onto a philosophical point: what does it mean to have part of a result set sorted and the rest unsorted? Literally, what would that look like?

Upvotes: 3

Related Questions