We are looking for guidance on a solution for enabling sorting of null values last when using an ORDER BY clause. Currently, nulls sort to the top of a list when using ORDER BY. See this example query.
This is a problem in the Explore Data section of Community Portals, because users select a facet of interest and then see null values for that facet at the top of the table. This is confusing for the users, and it would be preferable if they would see non-null values first.
We have investigated a few SQL based solutions to sort nulls last, but these currently fail-- Comments copied from PORTALS-244, from
_I didn’t find a sql based solution that works today (tried solutions from https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last):
Parsing failures from these attempts:
SELECT study, dataType, assay, id AS file_id, consortium as program, grant, species, organ, tissue, cellType, fileFormat, specimenID FROM syn11346063 ORDER BY ISNULL (organ), organ ASC
SELECT study, dataType, assay, id AS file_id, consortium as program, grant, species, organ, tissue, cellType, fileFormat, specimenID FROM syn11346063 ORDER BY organ IS NULL, organ ASC
I think we need to explore other options, or to extend the sql parser to support this.
Another option would be to use CASE statements to sort NULLs last; but currently this strategy also fails.