idea Sort rows in existing feature class in ArcGIS Pro Ideas

Sort rows in existing feature class

Bud — Fri, 26 Jan 2024 15:02:19 GMT

ArcGIS Pro 3.2.1; file geodatabase

As far as I can tell, there isn't a geoprocessing tool that can sort/reorder a FC's existing rows in a single step. For example, permanently sort a FC based on a number field.

The Sort tool outputs a separate FC, it doesn't sort the rows in an existing FC.

Could a "sort existing" tool be added?

Re: Sort rows in existing feature class

Bud — Fri, 26 Jan 2024 14:49:11 GMT

For what it's worth, adding an attribute index to a field in a mobile geodatabase table seems to sort the rows descending. (However, if the index is deleted, then the data seems to revert back to its original order.)

(I'm not used to seeing the actual data being sorted due to an index, since I normally make indexes using Oracle data. If I understand correctly, ascending or descending doesn't matter for Oracle indexes, except for composite indexes.)

I left the Ascending checkbox blank when creating that index.
I wonder why it sorted the data descending, despite the index being sorted ascending, as shown in a SQL client:

Re: Sort rows in existing feature class

JoshuaBixby — Tue, 23 Jan 2024 16:49:57 GMT

This idea isn't really practical, and anything that looks like reordering in-place would actually be a multi-step recreation, renaming, and deletion bundled together, which wouldn't be any different than doing those steps manually oneself.

In terms of SQL itself, there is no default order for records. Looking to the section of the SQL standard that defines the behavior of cursors, ISO/IEC CD 9075-2 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation), it clearly states (at least through SQL:99 that I have seen in person, but I assume the same language exists in later editions):

When the ordering of a cursor is not defined by an <order by clause>, the relative position of two rows is implementation-dependent.

The importance of that statement can't be emphasized enough. The default ordering of records in a table is implementation dependent, and most vendors will state somewhere they don't guarantee a default ordering. The Sort (Data Management)—ArcGIS Pro | Documentation gives a false-sense of assuredness to users because a table can be reconstructed a certain way, but Esri cannot guarantee the default ordering for every type of data store. The main value, in my mind, of the Sort tool is to support spatial reordering of records so that indexing can be optimized to improve analysis performance in certain situations.

Do records in a file geodatabase tend to have a predicable default ordering? Yes, but it is an implementation artifact tied to ObjectID ordering and indexing. I am not sure if Esri has ever documented a guaranteed default ordering, so if order matters an ORDER BY clause should always be used.

Re: Sort rows in existing feature class

SSWoodward — Thu, 25 Jan 2024 19:46:50 GMT

@JoshuaBixby is correct.

Any concept of 'permanently' reordering rows, or fields, or something of the like in a database would be a full delete and rewrite of the entire table.

Can you share with us what workflow of yours would be improved by this? As you continue to add data, would you again want to keep deleting and rewriting the table to sort?

Re: Sort rows in existing feature class

Bud — Fri, 26 Jan 2024 17:54:37 GMT

@SSWoodward

I have a small UNIT_RATE table that users edit once per year.

When looking at the attribute table, the data doesn't make much sense when it's unsorted:

It only makes sense when it's sorted into groups. The user would navigate to a specific group and add an entry for the current year.

Sort into groups using a custom sort:

Now the data makes more sense:

When users go to edit the table each year, it's unrealistic to get them to do the custom sort in the attribute table each time. They just don't remember, get confused when they look at the data, and end up botching their edits.

I'd rather sort the table permanently myself, once per year, prior to the year-end edits. Either manually or on a schedule.

Edit: I'm aware that maps/projects, .LYRX files, etc. can store sorting information. But we don't use those mechanisms in this case; we add the item from Catalog into various maps, as needed, since the table is only used occasionally.

Re: Sort rows in existing feature class

Bud — Fri, 26 Jan 2024 14:56:11 GMT

@SSWoodward

As mentioned in this post: "You don't have any notifications", there is an issue where the Esri Community system is not always sending email notifications about replies to posts.

Could you reply to this comment as a test, and then if I receive the reply, I'll let the Esri Community team know?

Re: Sort rows in existing feature class

SSWoodward — Fri, 26 Jan 2024 17:12:24 GMT

Here is my reply! I won't @ you so we can tests a standard reply to your post.

Re: Sort rows in existing feature class

Bud — Mon, 12 Feb 2024 19:11:49 GMT

Re: Sort rows in existing feature class

Bud — Fri, 22 Mar 2024 11:56:09 GMT

Control order of FC features in popup (show newest feature first)