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)