49

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?

danijar
  • 32,406
  • 45
  • 166
  • 297
JM4
  • 6,740
  • 18
  • 77
  • 125
  • Well is it possible your query would return both types? And if not would you not say there are two different queries one for each type then its easy to make a order by clause for each. – Iznogood Aug 23 '10 at 19:23
  • @iznogood - we do this in the event that the type or sort is known ahead of time (think sort by letter of name) but this does not seem to work in this particular case. For example: $query = "SELECT * FROM table WHERE STATUS = 'Active' AND ((LNAME LIKE 'A%' AND PARTY = 'Member') OR (GROUPNAME LIKE 'A%' AND PARTY = 'Group')) ORDER BY KDATE ASC"; does work } – JM4 Aug 23 '10 at 19:28
  • @JM4: Did you change the `KDATE` column to a more appropriate column type yet (Such as either `DATE` or `DATETIME`)? – ircmaxell Aug 23 '10 at 19:31
  • @ircmaxell - I did on some new tables and am in the process of migrating old tables to the new format. The primary issue has been monitoring a 'live' site and database and figuring the best way to modify the PHP scripts along with database structures without messing up a site that takes 400 registrations a day. – JM4 Aug 23 '10 at 19:34
  • @JM4: Progress is the important thing. Rome wasn't built in a day. I'm glad to hear that at least things are progressing down the better path... – ircmaxell Aug 23 '10 at 19:35
  • thanks for the encouragement! Its been a chore but I think it will be well worth it in the end. I am also taking your advice, going back and restructuring entire databases to remove 'text' types and replace with limited varchars (for things like first and last names) – JM4 Aug 23 '10 at 19:44

2 Answers2

85

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

ircmaxell
  • 163,128
  • 34
  • 264
  • 314
  • Perhaps I am misusing but I get an error when using the suggested format as: "SELECT * FROM table WHERE STATUS = 'Active' ORDER BY CASE TYPE WHEN 'Member' THEN LNAME ASC WHEN 'Group' THEN GROUPNAME ASC ELSE 1 END" – JM4 Aug 23 '10 at 19:35
  • 1
    Nooo, the `ASC` goes after the `END`. Sorry, I'll edit the answer to make that more clear... – ircmaxell Aug 23 '10 at 19:36
  • 1
    I actually went with the top suggestion above as it fits perfectly in fewer steps. Thanks! – JM4 Aug 23 '10 at 20:15
12

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;
Andy
  • 856
  • 9
  • 26