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.

[KoryKramer]

You could select features where the date is not null, then switch to Show Selected Records and sort on the date field there:

[Bud]

@KoryKramer 

Yeah, good point. That would work in a pinch.

It's still maybe a bit clunkier than I'd like though. And takes an extra step.

In many cases, I still want to see the nulls too, I just want them to be at the bottom of the list, while sorting descending.

Thanks for the tip though.

[KoryKramer]

Understood - just sharing some thoughts to give you something to work with.

[Bud]

I tested a different workaround, and it technically worked as intended. But it was far to slow to use:

1. Create a query layer with a NULL_FLAG field:

select
    objectid,
    last_edited_date,
    case when last_edited_date is not null then 1 else 0 end as null_flag
    --Source: https://modern-sql.com/concept/null#order-by
from
    events

2. Join from the table to the query layer via the OBJECTID.

3. Do an advanced sort using the fields from the query layer:
 - NULL_FLAG descending.
 - LAST_EDITED_DATE descending.
 

Now, the latest dates are at the top and the nulls are at the bottom. Which is what I wanted.

But like I said, it's far too slow to use. It takes roughly a minute to do the sorting, whereas sorting was instantaneous without the workaround (and sorting was instantaneous when the query was tested in the DB too). So, something about joining and sorting on the query layer is going awry.

Esri Case #03092490 - Attribute table join (and sort) between two EGDB tables [performance issues]

Related: 

• Join from FC to mobile GDB database view results in buggy attribute table behavior
• Perform join at the database level, not the application level, when the datasets are in the same wor...
• Virtual Attributes (ad hoc)
• BUG-000160320: Slow performance in Pro when interacting with the attribute table of data which participates in a join.

[Bud]

Related: Promote selected records

[AndrewQuee]

I just ran into this while working with a client's priority job.  I did not enjoy having to scroll through 11 thousand null records after sorting 'descending value' to verify the record.

Yes I know why it is why it is like this, yes I use SQL, and yes I know of the workarounds suggested, but common sense says any value is greater than null, surely?  Nulls last for me, always, or at least give us an option to manage this as per SQL.

And no, I do not want to restrict my data, I will need those null values for other queries.

[Bud]

@AndrewQuee Feel free to upvote.

[AndrewQuee]

Is done, I have been voting ideas since they were invented on the 'ol geonet.

For now I have written some quick "IS NOT NULL" layer definition queries and using the Pro toggle option.  It's "good enough" but annoying to have to set for each project and layer. 

For ArcMap users you have to use the old "1=1 OR <query>" def'n query hack to toggle on each layer.

[Bud]

Interesting. Can you elaborate on the "using the Pro toggle option" part?