UNION ALL ordering final results

Question

I have the following query that produces results successfully. However, the results are consistent, and I would like to order them by column in both tables named clicks

 (SELECT women_id, title, body FROM women ORDER BY women_id DESC LIMIT 5) UNION ALL (SELECT man_id, title, body FROM man ORDER BY man_id DESC LIMIT 5) 

Now he gives the following results:

 women 41124 women 124 women 12 women 2 women 1 men 3455434 men 1233 men 135 men 23 men 1 

I need it to give final results as follows:

 men 3455434 women 41124 men 1233 men 135 women 124 men 23 women 12 women 2 women 1 men 1 

Answer 1

You need to enter clicks into the UNION -ed dataset, add ORDER BY , and then project the unwanted column using another SELECT , for example:

 SELECT person_id, title, body FROM (( (SELECT women_id as person_id, title, body, clicks FROM women ORDER BY women_id DESC LIMIT 5) UNION ALL (SELECT man_id as person_id, title, body, clicks FROM man ORDER BY man_id DESC LIMIT 5) ) ORDER BY clicks DESC) data 

Answer 2

 ( (SELECT women_id, title, body, clicks FROM women ORDER BY women_id DESC LIMIT 5) UNION ALL (SELECT man_id, title, body, clicks FROM man ORDER BY man_id DESC LIMIT 5) ) ORDER BY clicks DESC 

just add the order at the end and the work done

Answer 3

 SELECT * FROM ( (SELECT women_id AS ID, title, body FROM women LIMIT 5) UNION ALL (SELECT man_id AS ID, title, body FROM man LIMIT 5) ) ORDER BY ID DESC