Specify how nulls are sorted in the attribute table (nulls first or last)

Bud

When sorting a field in the attribute table, it would be helpful if we could specify how nulls are handled (NULLS FIRST vs. NULLS LAST).

For example (Oracle), I want to sort a date field descending so that the latest dates are at the top. But when I do that, 1) nulls are at the top, then 2) the latest dates are shown next, then 3) the first dates. 

That's a nuisance when dealing with large tables. I need to painstakingly scroll down through nulls to get to the first instance of a non-null date in the list. (In my case, I had to scroll through 10,000 nulls...and then look for where the nulls stop and the dates start)

Sorting in ascending order doesn't help either.

It would be better if I could specify that I want the nulls to be last in the list, even though I'm sorting descending.

Maybe something like this:

Would something like that be possible?

 

Related: Allow using the ORDER BY clause in definition queries (SQL)

Bud

For what it’s worth:

Oracle, PostrgreSQL, and DB2 have this functionality built right into the database. 

with data (id, last_edited_date) as (
select 1, to_date('2022-06-21', 'YYYY-MM-DD') from dual union all
select 2, null                                from dual union all
select 3, to_date('2022-06-21', 'YYYY-MM-DD') from dual)

select
    id,
    last_edited_date
from
    data
order by
    last_edited_date desc nulls last  

        ID LAST_EDITED_DATE
---------- ----------------
         1        21-JUN-22
         3        21-JUN-22
         2           [null]   

 

Indexing ASC, DESC and NULLS FIRST/LAST

Besides ASC and DESC, the SQL standard defines two hardly known modifiers for the order by clause: NULLS FIRST and NULLS LAST. Explicit control over NULL sorting was “recently” introduced as an optional extension with SQL:2003.

So, since that functionality is built into some databases, that might make implementing this feature easy…at least for those specific DBs.

Edit:

It turns out there is a way to do it in all databases:

Null in Order By

The effect of  nulls (first|last) can be obtained with a case expression in all databases. The following example implements order by c nulls first:

ORDER BY CASE WHEN c IS NULL
              THEN 0
              ELSE 1
          END
       , c

Note that the case expression defines a new order by key for the sole purpose of separating null and not null values.

So I wonder if that would make it easy to implement this functionality in ArcGIS Pro. Just construct a slightly different ORDER BY clause in the SQL that's sent to the database...using the above CASE technique.