MySQL Using ORDER BY while using UNION

Question

I'm trying to show a list of users where the list of accounts which are activated=1 are below while activated=0 accounts are on top. I tried using UNION since I need all inactive accounts as ORDER BY created DESC while activated accounts set as ORDER BY email but for some reason all my ORDER BY commands are being ignored. What am I doing wrong here?

(
    SELECT 
        email, 
        roles.full role, 
        created 
    FROM sol_users users
    JOIN sol_user_roles ur ON users.id = ur.user_id 
    JOIN sol_roles roles USING(role_id) 
    WHERE activated = 0
    ORDER BY created DESC
)
UNION
(
    SELECT
        email, 
        roles.full role, 
        created
    FROM sol_users users
    JOIN sol_user_roles ur ON users.id = ur.user_id 
    JOIN sol_roles roles USING(role_id) 
    WHERE activated = 1
    ORDER BY email
)

Inactive users need to be placed on top so the Admin will know that these need to be activated.

user359040 · Accepted Answer · 2013-04-27 10:33:10Z

2

You don't need a union - try:

SELECT email, roles.full role, created 
FROM sol_users users
JOIN sol_user_roles ur ON users.id = ur.user_id 
JOIN sol_roles roles USING(role_id) 
ORDER BY activated, case activated when 1 then email end, created DESC

answered Apr 27, 2013 at 10:33

user359040

Sign up to request clarification or add additional context in comments.

1 Comment

enchance Over a year ago

Woah. I never knew you could use conditionals for ORDER BY. Such beautiful code!

luksch · Accepted Answer · 2013-04-27 10:37:25Z

0

You can try to use an IF statement in your ORDER BY clause. See this for a discussion about a similar topic.

answered Apr 27, 2013 at 10:37

luksch

11.7k6 gold badges41 silver badges54 bronze badges

Collectives™ on Stack Overflow

MySQL Using ORDER BY while using UNION

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related