Mysql sorting on multiple columns with IF-ELSE


Posted on Feb 25th, 2015


Recenty, I faced a scenario where I wanted to sort the rows of a mysql table in decending order based on two columns. My mysql table looked like this. Over here, end_no cannot be greater than start_no.

mysql> SELECT * FROM users2;

+----+----------+--------+
| id | start_no | end_no |
+----+----------+--------+
|  1 |        1 |   NULL |
|  2 |        5 |   NULL |
|  3 |        8 |      3 |
|  4 |        7 |      4 |
+----+----------+--------+
4 rows in set (0.00 sec)

I wanted to sorted this table based on the below conditions:

  • If end_no is null, then start_no should be considered.
  • If end_no is not null, then end_no should be considered.
  • Based on the above two conditions, my table should be sorted in descending order.

I am expecting the resultset to be this:

+----+----------+--------+
| id | start_no | end_no |
+----+----------+--------+
|  2 |        5 |   NULL | => 5
|  4 |        7 |      4 | => 4
|  3 |        8 |      3 | => 3
|  1 |        1 |   NULL | => 1
+----+----------+--------+

So I tried the below queries:

 

mysql> SELECT * FROM users2 
        ORDER BY start_no DESC, end_no DESC;

+----+----------+--------+
| id | start_no | end_no |
+----+----------+--------+
|  3 |        8 |      3 |
|  4 |        7 |      4 |
|  2 |        5 |   NULL |
|  1 |        1 |   NULL |
+----+----------+--------+
4 rows in set (0.00 sec)

I also tried using GREATESTLEAST but nothing worked.

Finally the one that worked for me was this:

mysql> SELECT *,IF( end_no IS NULL, start_no, end_no) AS nums 
           FROM `users2` 
           ORDER BY nums DESC;

+----+----------+--------+------+
| id | start_no | end_no | nums |
+----+----------+--------+------+
|  2 |        5 |   NULL |    5 |
|  4 |        7 |      4 |    4 |
|  3 |        8 |      3 |    3 |
|  1 |        1 |   NULL |    1 |
+----+----------+--------+------+
4 rows in set (0.00 sec)

In the above mysql query, the if else condition works like this:

IF(condition, true,false)

Conditions in mysql are great way of sorting your data but this really depends on scenarios. Most of the scenarios can be covered by sorting on one column and for that we need a simple ORDER BY COLUMN. But for complex sorting scenarios, conditions are the way to go.

 


Setting up hosting environment on Amazon EC2 with NodeJS, Nginx and php-fpmGoals are evil

Comments
100% Complete