Version: 11.2
Seemingly a very basic thing. But I couldn't do it :)
In the below UNION example an automatic sorting is happening based on alphabetic order. I want to get rid of this sorting and list the values according to the order of the table in the SELECT.
Test data
create table test1 (name1 varchar2(10));
create table test2 (name2 varchar2(10));
create table test3 (name3 varchar2(10));
insert into test1 values ('EARTH');
insert into test1 values ('TAURUS');
insert into test2 values ('YELLOW');
insert into test2 values ('ALPHA');
insert into test2 values ('TANGO');
insert into test3 values ('BRAVO');
select name1 from test1
union
select name2 from test2
union
select name3 from test3;
NAME1
----------
ALPHA
BRAVO
EARTH
TANGO
TAURUS
YELLOW
6 rows selected.
From the above example, I want the values from first table in SELECT to listed first , then all the values from the second table in the SELECT, and so on.
Basically my requirement will be
Return all values from test1 (alphabetically sorted)
then
Return all values from test2 (alphabetically sorted)
then
Return all values from test3 (alphabetically sorted)
The expected output:
NAME1
----------
EARTH ------------> from the first table in the SELECT
TAURUS ------------> from the first table in the SELECT
ALPHA ----------------------> from the second table in the SELECT
TANGO ----------------------> from the second table in the SELECT
YELLOW ----------------------> from the second table in the SELECT
BRAVO ------------------------------> from the third table in the SELECT