Using MySql, can I sort a column but have 0 come last?

Breaking down the fast answer

That one-liner leverages MySQL's treatment of true as 1 and false as 0. The boolean test (column_name = 0) returns 1 for zero values and 0 for all others. This sorting tactic first separates the zeros, then sorts the rest of the data in natural order.

Advanced sorting: grappling with corner cases

Sorting with negatives and outliers

Good news! The solution works even if your data includes negative values or other outliers. Here's a IF function tweak to handle these cases:

ORDER BY IF(column_name = 0, 1, 0), IF(column_name < 0, -1/column_name, column_name);


Note: -1's here are like quantum physics. Everything is counter-intuitive. 😄

Using null to sort zeros

MySQL places NULLs at the end. You can exploit this by transforming zeros into NULLs for the sort:

ORDER BY IF(column_name = 0, NULL, column_name);


Chew on this: transformation turns NULLs into sorting superheroes! 🦸‍♂️

Using UNION to sort

For more complex queries or different sort orders, UNION can work wonders:

(SELECT * FROM table_name WHERE column_name != 0 ORDER BY column_name)
UNION ALL
(SELECT * FROM table_name WHERE column_name = 0)


This first selects and sorts the non-zero rows, then appends the zero ones.

Scaling up: when zero is not alone

When zero isn't the loner

When there are multiple special values, expand the CASE expression:

ORDER BY CASE
  WHEN column_name = 0 THEN 2
  WHEN column_name = -1 THEN 1
  ELSE 0
END, column_name;


A party where -1 arrives before 0 and all others follow. 🥳

Handling heavy zero-laden data sets

For a zero-army in your data, row-wise conditioning might slow you down. Here, a derived table can minimize this:

SELECT your_fields
FROM (
  SELECT *, (column_name = 0) AS zero_last
  FROM table_name
) AS sorted_table
ORDER BY zero_last, column_name;


Someone once said: "Luxury is… a well-optimized SQL." 😎