Sort Null Values to the End of a Table

Sort NULL values to the end of a table

NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values on top.

PostgreSQL 8.3 introduced NULLS LAST:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

See:

• Sort by column ASC, but NULL values first?
• The manual on SELECT

How to sort a column in ascending order with NULL values at the end?

If you calculate anything on the date field, you'll loose the use of the index on that.

So check for Null:

SELECT 
    startdate, id
FROM 
    YourTable
ORDER BY
    StartDate Is Null,
    StartDate

How to handle null values inside my sort by function?

The challenge is about sorting numbers OR text with the same function, based on a column name and a direction. The difficulty is about making sure we deal with a number or with text... to apply the right .sort() callback.

Additionally, there are two things to take in account:

• Some strings may need to be "reformatted" as numbers
• Some values may be null and should always must ends up at the end of the sorting.

sortedRowInformation(data, getComparator(orderDirection, valueToOrderBy)).map(...)

That sortedRowInformation really looks like a .sort() function nested in a .sort() function... With two .map() applied on a stabilizedRowArray sub-array...

I have to admit I discarded it right away. I assumed the expected result by its pretty descriptive name and the 1st argument being the data.

The second argument... Where getComparator() is a function call with the two componant state properties as argument. That returns two possible functions (lol, the same function with or without a minus sign in front of its evaluation...). So calling that descendingComparator function again calls another function numeric() where our null problem is.

And all this process to just return -1, 0 or 1 to be used as that second argument for sortedRowInformation()... Which is the basic .sort() internal job.

Just describing this process should raise a big flag. You nicely overcomplicated everything.

Solution

The numeric() function is okay to isolate as a function. But that is where you had difficulties due to the null values coming in 3 functions above... So what if we put the null values aside from that logic and always assume a non-null value?

So now that we decided there is no null values, we can test them more easily for number or string. Anyway there is a special case where a string can finally be a number when removing the $ and comas (thousand separator).

I came up with this custom isNumeric() function:

function isNumeric(x) {
  let value = !isNaN(x) ? x: parseFloat(x.replace(/[\$,]/g, ""))
  return {isNum:!isNaN(value), value}
}

That function returns an object containing a boolean and a "processed" value: a number or NaN.

Now... Getting back to the starting point for the sorting, here is a function to sort the data:

function sortAll(data, orderDirection, valueToOrderBy) {
  
  // Filter the nulls in an array and the rest in another
  let nulls = data.filter((item) => item[valueToOrderBy] == null)
  let toSort = data.filter((item) => item[valueToOrderBy])
  
  // Sort the non-null values
  let sorted = toSort.sort((a, b) => {
    
    // Check if both values are numeric
    let aa = isNumeric(a[valueToOrderBy])
    let bb = isNumeric(b[valueToOrderBy])
    
    // If numerics
    if (aa.isNum && bb.isNum) {
      return aa.value - bb.value
    }
    
    // If strings
    return (a[valueToOrderBy]).toLowerCase() > (b[valueToOrderBy]).toLowerCase() ? 1 : -1;
  });

  // The sorting direction
  if (orderDirection === "desc") {
    sorted.reverse();
  }
  
  //  Add the nulls at the end of the returned array
  return sorted.concat(nulls);
}

So to apply it in your React component return is:

sortAll(data, orderDirection, valueToOrderBy).map(...)

instead of:

sortedRowInformation(data, getComparator(orderDirection, valueToOrderBy)).map(...)

Squarely discard those functions: sortedRowInformation , getComparator and descendingComparator and replace numeric with my isNumeric.

Here is a CodePen where I tested the sorting cases with some feak data.

If it does not already work in all cases with your data... At least, it is way easier to improve. ;)

How to sort NULL values last - for grouping summary rows with group by rollup/cube

Just change

ORDER BY Year, Quarter

to

ORDER BY GROUPING(Year), Year, GROUPING(Quarter), Quarter

Explanation: this function returns 1 for total rows and 0 for all others.

custom sorting v-data-table with null values last

Your sorting algorithm is not working correctly for strings.

Imagine that your first string is null, and the second one is 'Jelly bean'.
Instead of null value you are trying to compate Infinity with 'Jelly bean'.

This comparison will be false in both cases:

let a = Infinity;
let b = 'Jelly bean';
console.log(a > b);
console.log(a < b);

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

 ngAfterViewInit() {
  this.dataSource.sortData = (data, sort: MatSort) => {
  let sortedData = [];
  sortedData = data.sort((a, b) => {
    const direction = this.getSortingOrder(sort.direction, a, b);
    if (!direction[0][sort.active] || !direction[1][sort.active]) {
      return sort.direction === "asc" ? 0 : -1;
    } else {
      return direction[0][sort.active]
        .toString()
        .localeCompare(direction[1][sort.active]);
    }
  });
  return sortedData;
   };
 }


  getSortingOrder = (order, a, b) => {
    const sorted = order === "asc" ? [a, b] : [b, a];
    return sorted;
  }