SQL table using two column sort with IF statement in sorting

In this example there is a table that has a column ‘start’ and a column ‘end’ has. Well according to ‘end’ are sorted and if ‘end’ has an empty value to the column ‘start’ in the sorting step in.

The mySQL statement to is:

 SELECT *, (CASE WHEN `end` NOT LIKE '0000-00-00' THEN `end` ELSE `start` END) as `sort` FROM `test_tbl` ORDER BY `sort` 

After the ‘*, ‘ is the CASE statement that the contents of ‘end’ returns if the contents of ‘end’ is valid (here: Not ‘0000-00-00’). If invalid, then the content of the field ‘start’ is returned. The whole result is stored by as as the new field ‘sort’. At the end of the SQL statement that is sorted with ORDER BY on the contents of the new field ‘sort’.

The table ‘test_tbl’ is as follows:

sql_table_content

The result of the SQL statement:

sql_result_order_by

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*
*