Using MySQL, How to Sort a Column But Have 0 Come Last

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

You may want to try the following:

SELECT * FROM your_table ORDER BY your_field = 0, your_field;

Test case:

CREATE TABLE list (a int);

INSERT INTO list VALUES (0);
INSERT INTO list VALUES (0);
INSERT INTO list VALUES (0);
INSERT INTO list VALUES (1);
INSERT INTO list VALUES (2);
INSERT INTO list VALUES (3);
INSERT INTO list VALUES (4);
INSERT INTO list VALUES (5);

Result:

SELECT * FROM list ORDER BY a = 0, a;

+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    0 |
|    0 |
|    0 |
+------+
8 rows in set (0.00 sec)

MySQL Orderby a number, Nulls last

MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:

SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC

It is essentially the inverse of position DESC placing the NULL values last but otherwise the same as position ASC.

A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by

MySQL: Order by field, placing empty cells at end

select * from table
order by if(field = '' or field is null,1,0),field

SQL: Sort by priority, but put 0 last

Try:

order by case priority when 0 then 2 else 1 end, priority

0 come last when sorting ascending

You can do it by testing for price-ordering twice:

SELECT * FROM Product P 
ORDER BY  CASE WHEN @OrderBy='Date ASC' THEN Date  END ASC,
          CASE WHEN @OrderBy='Price ASC' THEN CASE WHEN Price = 0 THEN 1 ELSE 0 END ASC,
          CASE WHEN @OrderBy='Price ASC' THEN Price END ASC,
          CASE WHEN @OrderBy='Title ASC' THEN Title  END ASC,
          CASE WHEN @OrderBy='' THEN Match END

By the way, the implicit value of the case expression when @orderBy doesn't equal the string is null. When the sort column contains all nulls, it effectively disables sorting for that attribute.

Order by position, but place 0 last

order by
   case when position = 0 then 2 else 1 end,
   position

First forces all zeros to come last,
THEN, order by position

mysql order by field with NULL values last

You can either:

1. Explicitly sort first by whether the column is NULL and then by its value:

   ORDER BY product_condition IS NULL, FIELD(...)
   

   This works because product_condition IS NULL will be 0 for non-NULL columns and 1 for NULL columns; and in the (default) ascending order, the former will obviously come first.

2. Rely on the fact that NULL is sorted last in descending orderings and reverse the arguments to FIELD():

   ORDER BY FIELD(product_condition, ...) DESC
   

---

Related Topics