How to ORDER BY with NULLS comes FIRST or LAST

Help
1

I would like to sort on a column and push nulls to the back of the resultset. In oracle I can do ORDER BY COLUMN NULLS LAST, is there something similar in MEMSQL? Thanks.

2

You can check for IS NULL in the ORDER BY clause.

Nulls first:

select name from people order by name asc

Nulls last:

select name from people order by name is null, name asc

Descending order can be achieved as well with this approach.

Nulls first:

select name from people order by name is not null, name desc

Nulls last:

select name from people order by name desc
1 Like
3

You can also accomplish this by negating the order by column and using desc. For example:

memsql> select * from t order by -a desc;
+------+
| a    |
+------+
|    1 |
|    2 |
| NULL |
+------+
3 rows in set (0.00 sec)

memsql> select * from t order by a;
+------+
| a    |
+------+
| NULL |
|    1 |
|    2 |
+------+
3 rows in set (0.08 sec)
4

Hi mpskovvang, thanks of the response. I tried “is null”, although the query went through, the result returned is not sorted.

5

Hi rob thanks for the input, any recommendations for Strings?

6

You could use a conditional expression and make a NULL come out as ZZZZ or something suitably large.

7

I see, thanks hanson!

8

I tried “is null”, although the query went through, the result returned is not sorted.

What was the query you used? Did you make sure to include both name is null and name in the order by list?

9

Hi Jack, good call, I only had name is null but not name. It now works, thanks!