Using MySQL, can I sort a column but allow 0 to come last?

You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −

select *from yourTableName order by yourFieldName = 0,yourFieldName;

To understand the above concept, let us create a table. The query to create a table is as follows −

mysql> create table SortColumnZeroAtLastDemo
   −> (
   −> RankNumber int
   −> );
Query OK, 0 rows affected (1.40 sec)

Now you can insert records in the table using the following query −

mysql> insert into SortColumnZeroAtLastDemo values(100);
Query OK, 1 row affected (0.20 sec)

mysql> insert into SortColumnZeroAtLastDemo values(0);
Query OK, 1 row affected (0.17 sec)

mysql> insert into SortColumnZeroAtLastDemo values(0);
Query OK, 1 row affected (0.11 sec)

mysql> insert into SortColumnZeroAtLastDemo values(50);
Query OK, 1 row affected (0.13 sec)

mysql> insert into SortColumnZeroAtLastDemo values(10);
Query OK, 1 row affected (0.15 sec)

mysql> insert into SortColumnZeroAtLastDemo values(0);
Query OK, 1 row affected (0.17 sec)

mysql> insert into SortColumnZeroAtLastDemo values(40);
Query OK, 1 row affected (0.12 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from SortColumnZeroAtLastDemo;

The following is the output −

+------------+
| RankNumber |
+------------+
|        100 |
|          0 |
|          0 |
|         50 |
|         10 |
|          0 |
|         40 |
+------------+
7 rows in set (0.00 sec)

Here is the query to sort column and set value 0 to be at last using the syntax we discussed in the beginning −

mysql> select *from SortColumnZeroAtLastDemo order by RankNumber = 0,RankNumber;

The following is the output −

+------------+
| RankNumber |
+------------+
|         10 |
|         40 |
|         50 |
|        100 |
|          0 |
|          0 |
|          0 |
+------------+
7 rows in set (0.00 sec)

Rishi Rathor

Published on 11-Jan-2019 11:25:18

• Related Questions & Answers
• How do I modify a MySQL column to allow NULL?
• How do I update the decimal column to allow more digits in MySQL?
• Can I get Age using BirthDate column in a MySQL query?
• How can I order in group but randomly with MySQL?
• How can I return 0 for NULL in MySQL?
• How can I tell when a MySQL table was last updated?
• How can we allow MySQL to store invalid dates?
• Can you allow a regex match in a MySQL Select statement?
• How can I set 0 if a query returns a null value in MySQL?
• Can I search for particular numbers in a MySQL column with comma separated records using a MySQL query?
• How can I drop an existing column from a MySQL table?
• How can I remove every column in a table in MySQL?
• MySQL select to count values equal to 0 and greater than 0 from a column?
• MySQL query to sort by certain last string character?
• How can I change the default sort order of MySQL tables?