Can you add an if statement in ORDER BY?

I am trying to achieve the following:

I have a single ORDER BY statement which could vary depending on the value stored in Column A.

For example:

if the Type is Member, sort by member last name if the Type is Group, sort by the Group Name

both in Ascending order.

My best guess开发者_运维知识库 for the final statement would be:

SELECT * 
  FROM table 
 WHERE STATUS = 'Active' 
 ORDER BY ((LNAME if TYPE = 'Member') OR (GROUPNAME if TYPE = 'Group')) ASC

I know this is incorrect but cannot find information elsewhere. Any ideas?

---

Well, you can use the IF function in MySQL (Note the emphasis on function since there's also an unrelated IF statement)...:

ORDER BY IF(TYPE='Member', LNAME, GROUPNAME) ASC

However, in this case it seems the better choice (From a flexibility standpoint) would be the CASE statement:

ORDER BY 
    CASE `type` 
        WHEN 'Member' THEN LNAME 
        WHEN 'Group' THEN GROUPNAME
        ELSE 1 END 
    ASC

Note that the entire block from CASE to END is to be considered as a single "unit". The result of which is what you're trying to sort against (Hence why the ASC comes after the block, rather than inside of it)...

---

Use the CASE statement.

Example from http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html:

SELECT id, first_name, last_name, birthday
FROM table
ORDER BY
-- numeric columns
CASE _orderby WHEN 'id' THEN id END ASC,
CASE _orderby WHEN 'desc_ id' THEN id END DESC,
-- string columns
CASE _orderby WHEN 'first_name' THEN first_name WHEN 'last_name' THEN last_name END ASC,
CASE _orderby WHEN 'desc_first_name' THEN first_name WHEN 'desc_last_name' THEN last_name END DESC,
-- datetime columns
CASE _orderby WHEN 'birthday' THEN birthday END ASC,
CASE _orderby WHEN 'desc_ birthday' THEN birthday END DESC;