SQL UNION to SORT by two different ORDER BY clauses and remove duplicates

Question

I've researched this for the past two days on stackoverflow and the web and still not getting my desired result. Im using ORACLE 11G. I have two SQL queries with two different ORDER BY clauses. Both must retain the correct sort order. I just want to combine the two and remove duplicates as well. I have tried UNION to remove duplicates from my query and it works but does not retain my sort order for both my combined queries. How do I remove duplicates and keep both correct sort orders for each SQL query? Here is my query below that sorts correctly but does remove duplicates. What do I need to do? All the 2018 dates repeat once throughout the query result set. I do not want duplicates. Thanks for the help.

Here are the two queries I combined below (This is the top query):

SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM FLIGHTS
WHERE to_date(scheduled_launch_date, 'DD-MON-YY')
BETWEEN add_months(trunc(sysdate, 'MON'),0) 
AND add_months(trunc(sysdate, 'MON'),6) 
AND DATA_VERSION_NAME = 'WORKING' 
AND sequence_nmbr >= 0
ORDER BY SCHEDULED_LAUNCH_DATE ASC;

This is the bottom part of the query:

SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM FLIGHTS
WHERE DATA_VERSION_NAME = 'WORKING'
AND SEQUENCE_NMBR >= 0
ORDER BY sequence_nmbr asc;

Then here is the combined query I came up with that does not quite work like I want it to. I just wish to remove duplicates from the query below. How can I do this?

WITH CTE_Sets AS (
SELECT 1 AS set_order, FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM flights
WHERE to_date(scheduled_launch_date, 'DD-MON-YY')
BETWEEN add_months(trunc(sysdate, 'MON'),0) 
AND add_months(trunc(sysdate, 'MON'),6) 
AND DATA_VERSION_NAME = 'WORKING' 
AND sequence_nmbr >= 0
UNION
SELECT 2 AS set_order, FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM flights
WHERE DATA_VERSION_NAME = 'WORKING'
AND SEQUENCE_NMBR >= 0
)
SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM CTE_Sets
ORDER BY
set_order,
CASE set_order
    WHEN 1 THEN SCHEDULED_LAUNCH_DATE END ASC,
case set_order
WHEN 2 THEN SCHEDULED_LAUNCH_DATE END ASC;

Result for my UNION query above:

FLIGHT_NMBR  SCHEDULED
------------ ---------
SpX-14       26-JAN-18
69P          09-FEB-18
SpX-DM1      09-MAR-18
54S          13-MAR-18
OA-9         14-MAR-18
55S          29-APR-18
SpX-15       06-JUN-18
SpX-DM2      22-JUN-18
70P          27-JUN-18
1A/R         20-NOV-98
2A           04-DEC-98

If that’s the exact query, note that you’re doing both sorts based on scheduled launch date, not one with that and other with sequence number — Sami Kuhmonen, Jan 12 '18 at 16:55

score 1 · Accepted Answer · answered Jan 12 '18 at 16:58

Do the work in the order by clause:

SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM FLIGHTS
WHERE DATA_VERSION_NAME = 'WORKING' AND
      sequence_nmbr >= 0
ORDER BY (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') BETWEEN add_months(trunc(sysdate, 'MON'), 0) AND add_months(trunc(sysdate, 'MON'), 6) THEN 1 ELSE 2 END),
         (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') BETWEEN add_months(trunc(sysdate, 'MON'), 0) AND add_months(trunc(sysdate, 'MON'), 6) THEN SCHEDULED_LAUNCH_DATE END),
         sequence_nmbr;

Note: You should be storing scheduled_launch_date as a date column not a character column.

Wow this worked perfectly and did exactly what I needed it to do. Thank you. Two days of frustration gone. — Chris M, Jan 12 '18 at 17:01

SQL UNION to SORT by two different ORDER BY clauses and remove duplicates

1 Answers1