CODEWITHSUNDEEP
sqloracle-database
TeachMeLifeSkillsCoachRyan
TeachMeLifeSkillsCoachRyan

Reputation: 7

How would I group three unions together by date in Oracle SQL?

Here's an image if it helps

I'm trying to join three tables of Japanese Sumo results together and order them by descending date from 2019. I managed to join them but can't figure out how to order them by date (BASHO). I'm working in Oracle Apex.

So far I've tried

Select a.* from sumo_2019 a
where a.Rikishi1_ID=12191
order by a.BASHO
Union
Select b.* from sumo_2018 b
where b.Rikishi1_ID=12191
order by b.BASHO
Union
Select c.* from sumo_2017 c
where c.RIKISHI1_ID=12191
Order by c.BASHO

and

Select a.* from sumo_2019 a
where a.Rikishi1_ID=12191
Union
Select b.* from sumo_2018 b
where b.Rikishi1_ID=12191
Union
Select c.* from sumo_2017 c
where c.RIKISHI1_ID=12191
Order by BASHO

but received errors both times (errors are 'Error at line 3/17: ORA-00933: SQL command not properly ended' for the first attempt and 'Error at line 9/10: ORA-00904: "BASHO": invalid identifier' for the second attempt

Upvotes: 0

Views: 53

Answers (1)

MT0
MT0

Reputation: 167972

You can UNION inside a sub-query and the ORDER the outer query:

SELECT *
FROM   (
  Select *
  from   sumo_2019
  where  Rikishi1_ID=12191
Union
  Select *
  from   sumo_2018 b
  where  Rikishi1_ID=12191
Union
  Select *
  from   sumo_2017 c
  where  RIKISHI1_ID=12191
)
Order by BASHO;

Which, for the sample data:

CREATE TABLE sumo_2019 (BASHO, rikishi1_id) AS
  SELECT DATE '2019-01-01', 12191 FROM DUAL;

CREATE TABLE sumo_2018 (BASHO, rikishi1_id) AS
  SELECT DATE '2018-01-01', 12191 FROM DUAL;

CREATE TABLE sumo_2017 (BASHO, rikishi1_id) AS
  SELECT DATE '2017-01-01', 12191 FROM DUAL;

Outputs:

BASHO RIKISHI1_ID
2017-01-01 00:00:00 12191
2018-01-01 00:00:00 12191
2019-01-01 00:00:00 12191

fiddle

Upvotes: 0

Related Questions