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;