[SOLVED] FireDAC Left Join TDBGrid Assending Sort Order of columns with no joined data are at the bottom?

FireDAC Left Join TDBGrid Assending Sort Order of columns with no joined data are at the bottom?

I setup a LEFT JOIN between two tables that are used in a TDBGrid and it all works except the sort order of the joined string column is not as expected. In ascending order the blank entries, those that didn't have a match on the joined table, are below items with text. Likewise if you sort in descending order they show at the top instead of the bottom. It seems like blank entries should be listed first when sorting on that column in ascending order? If not, what is the reason and is there a way to get it to sort as expected?

Solution

This is how your SQL engine returns that data. Remember that null values are considered an unfilled state instead of a real value.

The SQL standard allows you to add NULLS FIRST or NULLS LAST to your ORDER BY clause. Example:

ORDER BY LastName ASC NULLS FIRST

But many SQL engines doesn't recognize that clause, so you have to code a workaround, like converting the null values to empty strings, so they will be ordered normally.

ORDER BY coalesce(LastName, '') ASC