Can you add an if statement in ORDER BY?

Question

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?

Answer 1

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)...

Answer 2

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;