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)
Published on 11-Jan-2019 11:25:18
