Alex Alex - 4 months ago 8
MySQL Question

SQL UNION Statement below eachother

recently i´ve been experimenting around with the UNION function in sql statements, but i´ve come to a point where i cant get things how i want em.

so i´m trying to connect two querys together, one which gives me this output

ID | DATE | X
29 | 2016-07-31 20:00:00 | 0
27 | 2016-07-31 23:00:00 | 0
26 | 2016-07-31 23:22:00 | 0


Using this query :

SELECT * FROM table_entries WHERE closed='0' ORDER BY start_date ASC


And another one giving me this output

ID | DATE | X
28 | 2016-07-31 15:00:00 | 1
25 | 2016-07-31 01:00:00 | 1
24 | 2016-07-31 00:19:00 | 1


Using this query:

SELECT * FROM table_entries WHERE closed='1' ORDER BY start_date DESC


So i´ve tried combining them using UNION this way :

(SELECT * FROM table_entries WHERE closed='0' ORDER BY start_date ASC) UNION (SELECT * FROM table_entries WHERE closed='1' ORDER BY start_date DESC)


but instead of the expected statement of

ID | DATE | X
29 | 2016-07-31 20:00:00 | 0
27 | 2016-07-31 23:00:00 | 0
26 | 2016-07-31 23:22:00 | 0
28 | 2016-07-31 15:00:00 | 1
25 | 2016-07-31 01:00:00 | 1
24 | 2016-07-31 00:19:00 | 1


it gives me this return

ID | DATE | X
26 | 2016-07-31 23:22:00 | 0
27 | 2016-07-31 23:00:00 | 0
29 | 2016-07-31 20:00:00 | 0
24 | 2016-07-31 00:19:00 | 1
25 | 2016-07-31 01:00:00 | 1
28 | 2016-07-31 15:00:00 | 1


What should i change to accomplish what i wanted in the beginning, or to "bind" these statements together so they append below eachother.

Kind regards

Answer

You don't need UNION for this query at all:

SELECT *
FROM table_entries
WHERE closed IN ('0', '1')
ORDER BY closed,
         (CASE WHEN closed = '0' THEN start_date END) ASC,
         (CASE WHEN closed = '1' THEN start_date END) DESC;

It is very important to understand that SQL result sets have no inherent ordering (except in a few exceptional cases), unless the query has an ORDER BY clause. This is because SQL is based on the notion of unordered sets.

UNION, in particular, needs to remove duplicates, which is additional processing. However, you should never assume the ordering of a query unless you have an ORDER BY for the outer-most SELECT.