Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can you add an if statement in ORDER BY?

Tags:

php

sorting

mysql

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?

like image 733
JM4 Avatar asked Aug 23 '10 19:08

JM4


People also ask

Can I use ORDER BY in with clause?

@ric in Firebird you can put an order by in a with clause, the effects are just not guaranteed (the sort might be retained when no other steps in the query plan cause a sort).

Can I use ORDER BY before the WHERE condition?

The ORDER BY clause is used to get the sorted records on one or more columns in ascending or descending order. The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query. Use ASC or DESC to specify the sorting order after the column name.

Can we use two ORDER BY clause in query?

Discussion: If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query. After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary).

How do you use ORDER BY in case statement?

CASE Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 ELSE result END; ORDER BY: This keyword is used to sort the result-set in ascending or descending order. It sorts the records in ascending order by default.


2 Answers

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

like image 79
ircmaxell Avatar answered Oct 05 '22 21:10

ircmaxell


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; 
like image 27
Andy Avatar answered Oct 05 '22 21:10

Andy