NULLS LAST flag in Denodo, MemSQL, Spark, VectorWise, and XtremeData

Question

I am doing a pseudo-ORM for a personal project, and I'm compiling a list of compatibility checks for the 20+ different SQL dialects out there.

Currently, I am doing research on whether a dialect supports the NULLS LAST flag, i.e. when sorting a table, have null come out as the last item like 1, 2, 3, 4, null instead of null, 1, 2, 3, 4.

I have results for languages like DashDB/MySQL/MSSQL, etc (yes - they offer NULL LAST) however, the following dialects, I was unable to find the null-last-ability:

1. Denodo

2. MemSQL

3. Spark

4. VectorWise

5. XtremeData

A simple "yes" or "no" will suffice in your answer, however, if you could also write the query down on how to perform the NULL LAST operation, that would be great too!

Answer 1

I checked on MemSQL and afaik the NULLS LAST syntax is not supported. That said, you can achieve this by doing something like this:

SELECT * FROM foo ORDER BY IFNULL(bar, MAGIC_VALUE) ASC

Change MAGIC_VALUE so it sorts last/first depending on the data in the column. If you are ok with a perf hit, you can mutate the value such that if its not null the value always starts with a prefix that sorts high, and if it is null the value sorts lower than the prefix.

Same thing can be used to implement NULL FIRST.