Ordering by nullable values should order nulls before 0

Question

I am using templates views and optic library to display list of items. Now in result after ordering ascending I get something like this in output: 0.00, 1.55, null. The correct result should be: null, 0.00, 1.55.

My code is similar to this example: https://docs.marklogic.com/ModifyPlan.prototype.orderBy but some of employee ids are null.

I tried to use op.case but the code runs slower and the results are not correct. I think maybe I can change the tde template to get correct result.

Answer 1

Currently, null values will always be last, returning after the other ordered items.

However, there is an option to specify NULLS FIRST or NULLS LAST in an SQL ORDER BY.

You can apply those options to your optic query through .op.fromSQL() as a workaround in order to get the null values sorted first.

op.fromSQL("SELECT EmployeeID, FirstName, LastName FROM employees ORDER BY EmployeeID NULLS FIRST")
.result()