We're updating the issue view to help you get more done. 

Portals Explore data- need ability to sort nulls last

Description

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.
e.g.: https://dba-presents.com/index.php/databases/sql-server/36-order-by-and-nulls-last-in-sql-server

Environment

None

Status

Assignee

John Hill

Reporter

Anna Greenwood

Labels

None

Validator

Anna Greenwood

Release Version History

None

Sprint

Priority

Major