CODEWITHSUNDEEP
mysqlsql-order-by
enchance
enchance

Reputation: 30421

MySQL Using ORDER BY while using UNION

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.

Upvotes: 2

Views: 37

Answers (2)

luksch
luksch

Reputation: 11712

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

Upvotes: 0

user359040
user359040

Reputation:

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

Upvotes: 2

Related Questions