How to order DESC by a field, but list the NULL values first?

To order by a field and list the NULL values first, you need to use the following syntax. This will order in descending order −

select yourColumnName from yourTableName group by yourColumnName is null desc,yourColumnName desc;

To understand the above syntax, let us first create a table −

mysql> create table OrderByNullFirstDemo
   −> (
   −> StudentId int
   −> );
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table with the help of insert command. The query is as follows −

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

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

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

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

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

mysql> select *from OrderByNullFirstDemo;

The following is the output −

+-----------+
| StudentId |
+-----------+
|       100 |
|       200 |
|       150 |
|      NULL |
+-----------+
4 rows in set (0.00 sec)

Implement the syntax we discussed in the beginning to perform order in descending order and display the null valu efirst −

mysql> select StudentId from OrderByNullFirstDemo group by StudentId is null desc,StudentId desc;

The following is the output −

+-----------+
| StudentId |
+-----------+
|      NULL |
|       200 |
|       150 |
|       100 |
+-----------+
4 rows in set, 2 warnings (0.00 sec)

Vrundesha Joshi

e

Updated on: 30-Jul-2019

57 Views

Kickstart Your Career

Get certified by completing the course

Get Started