CODEWITHSUNDEEP
sqloracle-database
l0r3nz4cc10
l0r3nz4cc10

Reputation: 1283

Union and order by a column not selected

Let's say I have a table A, with columns a_1 and a_2.

I am only interested in the column a_1, so I can write something like this :

select a_1 from A;

However I want it ordered by column a_2, so I could write this :

select a_1 from A order by a_2 desc;

Now I want to do an union :

select a_1 from A where <some filters>
union
select a_1 from A where <some other filters>
order by a_2 desc;

This doesn't work anymore ("a_2": invalid identifier), so I change it to :

select a_1, a_2 from A where <some filters>
union
select a_1, a_2 from A where <some other filters>
order by a_2 desc;

The select a_2 part bothers me : I don't want it to show in the results, how can I hide this information ?

Upvotes: 3

Views: 1979

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

Conceptually, it doesn't make sense to order by a_2 after you've done a union on just a_1. From Oracle's perspective, it is entirely possible that for any distinct a_1 value in either query, there may be many different a_2 values returned. So there are many possible sort orders.

You can, of course, nest the query so that you only get a_1 in the results

select a_1
  from (select a_1, a_2 from A where <some filters>
        union
        select a_1, a_2 from A where <some other filters>) sub_q
 order by a_2 desc;

Of course, from a performance standpoint, it would make sense to use union all rather than union unless you really do want the look for and eliminate duplicate rows. And if both queries are really pulling from the same table, it probably makes more sense to use a single query with an or rather than writing a union of two different queries.

Upvotes: 2

jarlh
jarlh

Reputation: 44716

Say that the UNION on both columns produce:

a_1 a_2
  A   1
  A   3
  B   2

Then UNION on only a_1 gives

 a_1
   A
   B

How do you know where to put A according to column a_2's values 1 and 3?

When UNION (or DISTINCT), only selected columns are allowed in the ORDER BY!

However, some dbms products allow non-selected columns when UNION ALL!

Upvotes: 1

Related Questions