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

Previous Page Print Page