idea Specify how nulls are sorted in the attribute table (nulls first or last) in ArcGIS Pro Ideas

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

Bud — Wed, 03 Jan 2024 05:23:23 GMT

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?

Re: Sorting a field in the attribute table: Specify how nulls are handled (NULLS LAST vs. NULLS FIRST)

Bud — Tue, 21 Jun 2022 17:09:27 GMT

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.

Re: Sorting a field in the attribute table: Specify how nulls are handled (nulls last vs. nulls first)

KoryKramer — Tue, 21 Jun 2022 15:57:59 GMT

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

Re: Sorting a field in the attribute table: Specify how nulls are handled (nulls last vs. nulls first)

Bud — Thu, 23 Mar 2023 05:55:48 GMT

@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.

Re: Sorting a field in the attribute table: Specify how nulls are handled (nulls last vs. nulls first)

KoryKramer — Tue, 21 Jun 2022 16:33:02 GMT

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

Re: Sorting a field in the attribute table: Specify how nulls are handled (nulls first or last)

Bud — Wed, 03 Jan 2024 05:26:40 GMT

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]

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 workspace
Virtual Attributes (ad hoc)
BUG-000160320: Slow performance in Pro when interacting with the attribute table of data which participates in a join.

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

Bud — Wed, 03 Jan 2024 05:20:32 GMT

Related: Promote selected records

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

AndrewQuee — Wed, 11 Sep 2024 01:34:23 GMT

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.

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

Bud — Wed, 11 Sep 2024 13:37:20 GMT

@AndrewQuee Feel free to upvote.

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

AndrewQuee — Fri, 20 Sep 2024 05:56:35 GMT

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.

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

Bud — Fri, 20 Sep 2024 16:54:23 GMT

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

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

AndrewQuee — Wed, 25 Sep 2024 07:39:45 GMT

Sure! Here's the documentation for it: ArcGIS Pro: Set the active definition query

Here's the project in question. As you can see I have multiple '<attribute>IS NULL' queries defined. I can toggle them on and off fairly easily from Layer Properties.

Choosing an active definition query from the list of defined queries

This is a great feature in Pro for often-used views on a layer, noting you can only have one active at one time, so it's not quite a radio-button toggle panel. Previously you would need to have multiple layers with different definition (restriction) queries defined.

It would still be useful to leave nulls in and have nulls first or last as you can accidentally leave a restriction query on and them come back months later to wonder where the data went. 🙂