Ruby On Rails, Design, Simplicity, Web 2.0, Ajax, Mac and Tons of Pizza.

SQL IF conditional statement

Posted by Sandro Paganotti in Ruby on Rails - comments are closed

The challenge I had to fight today was about ordering. I had a table that contains a list of activities and the ordering rule I want to apply to show them was:

• 1st: the activities with an expected_end not null, ordered by expected_end
• 2nd: the activities without an expected_end, ordered by position and sub ordered by created_on

The trick I found was to use the mysql IF(expr1,expr2,expr3) function that works in the following way: the first expression is evaluated and, if the result is true, then the second expression is returned, otherwise the third.

Using this function I created this ORDER statement:

ORDER BY expected_end IS NULL, IF(expected_end IS NULL,position,expected_end), created_on

That works like a charm!

The IF statement belongs to a group of very useful MYSQL functions that includes also a CASE statement . All these functions can be used almost everywhere in you queries.

Permalink