CODEWITHSUNDEEP
mysqlsqlcase
AnteKo
AnteKo

Reputation: 57

Sql case with case Order by

select id, username, email, 
 case
 when username = email then 'true'
 else 'false' 
 end 
 from users

Is there a simple way to do when username = email then 'true' as order_col or from users where case = true

The question is can you wrap case in AS caseand then order it or use Where statement on that case.

Upvotes: 0

Views: 82

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460068

You can use the alias in the ORDER BY but not in the WHERE, so this works:

SELECT id, username, email, 
       CASE 
         WHEN username = email THEN 'true' 
         ELSE 'false' 
       end AS UserNameIsEmail 
FROM   users 
ORDER  BY UserNameIsEmail DESC,
          username ASC

If you want it to use in the WHERE either repeat it or use a sub-query:

SELECT id, username, email, usernameisemail 
FROM  (SELECT id, 
              username, 
              email, 
              CASE 
                WHEN username = email THEN 'true' 
                ELSE 'false' 
              end AS UserNameIsEmail 
       FROM   users)X 
WHERE  X.usernameisemail = 'true' 
ORDER  BY username ASC

Upvotes: 1

juergen d
juergen d

Reputation: 204756

 select id, username, email, 
             case when username = email 
                  then 'true'
                  else 'false' 
             end as usr_is_email
 from users
 order by usr_is_email

Upvotes: 1

Related Questions