CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10581

UNION ALL ordering the end results

I have the following query which is producing results successfully. However, results are sequential and I would like to order them by a column in both tables called 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)

Right now it gives results like this:

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 like this:

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

Upvotes: 4

Views: 87

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

You need to bring in the clicks into the UNION-ed data set, add ORDER BY, and then project the unnecessary column out with another SELECT, like this:

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

Upvotes: 5

StilesCrisis
StilesCrisis

Reputation: 16290

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

Upvotes: 0

Ochi
Ochi

Reputation: 1478

(
    (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 order by at the end and job done

Upvotes: 0

Related Questions