nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select maximum values in Select By Attributes (greatest n per group)

Bud

ArcGIS Pro 3.2.1; mobile geodatabase

I'm trying to learn about different ways to select the greatest n per group in ArcGIS Pro. I came across this old ArcMap article that talks about a possible technique:

Select minimum and maximum values in the Select By Attributes window in ArcMap
Use GROUP BY subquery clauses to return values from aggregate functions on sets of values.
Example #3:
Select the records from the table with the most recent date for sets of values, where <group_ID> is the field that contains the values that define the groups:
<date> = (SELECT MAX(date) FROM <table_name> GROUP BY <group_ID>)

I'll use the SQL expression above to try to select the latest row per species group. The result looks like this:

t_date = (
    select 
        max(t_date) 
    from 
        species_records
    group by
        t_species)

That's not the result I was looking for.

This would be the result I'm looking for:

t_date in (
    select
        max(t_date)
    from
        species_records s2 
    where
        s2.t_species = species_records.t_species)

What am I missing in that article?

Notes:

I'm aware that there will be duplicate rows selected per group if there are multiple rows per species that have the same date. Breaking the tie (arbitrarily) would be ideal. But I don't know if that's possible with this particular approach.
I've attached sample Excel data that can be loaded into a geodatabase.

Thanks.

species_records.xlsx

MappyIan_1-1731512118411.png

Encontre mais publicações marcadas com

Mobile geodatabase

Accepted answers

JoshuaBixby

Wow, whoever wrote that Support article needs to brush up on their SQL. The logic of that SQL query is flawed because it could select multiple records for a given set if a set had a non-maximal date that happened to be a maximal date for another set. Usually when people are selecting maximum per group they want only the maximum selected (or multiple if maximum is shared), and not the maximum and maybe some others as well.

All comments

JoshuaBixby

DavidPike

I'm really struggling with subqueries in these FGDB views. They seems temperamental with warnings about 'cannot be edited in clause mode' when I'm not in clause mode, or working then not working on a whim. I thought I understood the limitation was only scalar results could be returned by the subquery, but then I see otherwise.

Bud

Yes, I’ve been struggling with FGDB views as well. I talk about the issues I’ve been having in my reply here:

Make Query Table — Use date field in join

Summary: Creating a “view on a view” (joining on multiple fields) in an attempt to get around the “no correlated subqueries” limitation didn’t work for me. Views on views don’t seem to work.

Bud

One thing that has helped me when writing FGDB SQL is to do a parallel test in a different geodatabase type where I can use a SQL client, such as DBeaver for mobile GDBs or SQL Developer for Oracle EGDBs. I put exactly the same data in a mobile GDB, mock up the SQL in DBeaver, then try to get something similar working in FGDB SQL.

It really helps me to write SQL in a SQL client that has proper error messages; it lets me make sure my syntax is correct in a normal environment first. FGDB SQL is challenging enough as it is; typos on my part make it so much worse, so I want to fix them ahead of time before dealing with FGDB SQL.

JoshuaBixby

The screenshot with the desired outcome appears to be greatest-n-per-group with "n" being 1 where the greatest has ties. Are you interested in greatest-singular or greatest-n? If the former, it simplifies the situation a bit.

UPDATE: The following is a generalized greatest-n-per-group that supports ties in SQLite. I can't speak to its performance, and it is a bit ugly with the nested subquery (nesting is forced with a window function), but it works and is a place to start:

ObjectID IN (
  SELECT
    ObjectID
  FROM
    (
      SELECT
        ObjectID,
        DENSE_RANK () OVER (
          PARTITION BY t_species
          ORDER BY t_date DESC
        ) AS date_rank
      FROM
        speciesrecords
    ) 
  WHERE 
    date_rank <= 1
)

The date_rank controls the "n" being selected.

Bud

Thanks! It's good to have solutions for both scenarios:

Has ties
Or, no ties

I'll follow up on your question and #2 in a separate reply.

But first, just to clarify regarding #1, are there any issues with the SQL expression I provided in the original post (the expression I wrote, not the one from the article).

t_date in (
    select
        max(t_date)
    from
        species_records s2 
    where
        s2.t_species = species_records.t_species)

I've always had a bit of a mental block when it comes to subqueries in the WHERE clause. When I look at my solution, it seems like it shouldn't work. But maybe it does?

In other words, is the resultset from my expression any different than the resultset from yours?

Bud

@DavidPike

I'm really struggling with subqueries in these FGDB views. They seems temperamental with warnings about 'cannot be edited in clause mode' when I'm not in clause mode

My understanding is that clause mode is the non-SQL mode. I think it's the mode where you construct a query using the UI pick lists.

If I'm writing a complex SQL query in SQL mode, then it makes sense to get the message, "The expression can't be edited in clause mode", since that complex SQL wouldn't be parsable into the UI pick lists. I just ignore that message.

Are you seeing something different?

Bud

Here's a mobile geodatabase technique for #2 - no ties.

species_records.objectid IN (
         SELECT objectid
           FROM species_records r2
          WHERE r2.t_species = species_records.t_species
       ORDER BY T_date DESC  --, priority ASC
          LIMIT 1
                )

And a similar technique that works for Oracle:

species_records.objectid IN (
         SELECT objectid
           FROM species_records s2
          WHERE s2.t_species = species_records.t_species
       ORDER BY t_date DESC --, priority ASC
          FETCH FIRST ROW ONLY
                )

JoshuaBixby

For n=1, the results of your query and mine are the same. That said, there are differences. As I already mentioned, your query is only applicable for n=1 so it would not work in other n-cases. Your query relies on a correlated subquery whereas mine relies on a couple co-routine calls and a couple subquery scans. The correlated subquery is an expensive operation, but having more execution steps like in mine can add up as well. How the two approaches perform relative to data set size? I don't know.

Since SQLite supports a row value on the left-hand side of the IN operator, an alternative to n=1 that doesn't require a correlated subquery is:

(t_species, t_date) IN (
  SELECT 
    t_species, 
    max(t_date) 
  FROM 
    species_records 
  GROUP BY 
    t_species
)

Bud

Since SQLite supports a row value on the left-hand side of the IN operator, an alternative to n=1 that doesn't require a correlated subquery is:
(t_species, t_date) IN (
  SELECT 
    t_species, 
    max(t_date) 
  FROM 
    species_records 
  GROUP BY 
    t_species
)

Very interesting. Thanks.

Mobile Geodatabase/SQLite:

For my notes, I'm confirming that the approach works in a mobile geodatabase. Greatest 1 per group (with ties), as expected.

Oracle 18c:

I was pleasantly surprised to see it also works in Oracle 18c. I didn't think Oracle supported "a row value on the left-hand side of the IN operator". But it does.

Oracle 18c demo: https://dbfiddle.uk/GznqkwlQ

Edit: @JoshuaBixby, you might find this video on Oracle's KEEP clause interesting. The KEEP clause will KEEP your SQL queries SIMPLE!

File Geodatabase:

Unsurprisingly, it doesn't work in file geodatabases.

SQL Server:

I couldn't get it to work in SQL Server: https://dbfiddle.uk/dtiYKrf_

Postgres:

From a quick googling, it looks like that syntax isn't supported in PostgreSQL either.

Bud

I'd like to ask a moderator to move this string of replies to our FGDB view post, since this current post is mostly about mobile geodatabases, not FGDBs. Is that ok with you?

Move to: Selecting the most recent records based on unique values in another field

Bud

Regarding not being able to create a database view with CAST in the WHERE clause, that is interesting because CAST works in the Select By Attributes tool, which of course is the WHERE clause:
CAST(t_date AS CHAR(20)) IS NOT NULL

I looked at the Diagnostic Monitor in ArcGIS Pro to see what SQL query Pro sends to the database when using CAST in Select By Attributes. I had wondered if it was doing something special, such as wrapping something in a subquery or outer query, which can be an effective workaround in some cases.

Examples:

Create FGDB database view using the ORDER BY clause: ERROR 999999
There is an Esri bug where ST_GEOMETRY functions in Oracle can’t be used in a SQL Expression (definition query, attribute table, etc.) when the FC is versioned. Wrapping the WHERE clause in a subquery lets us work around that bug.
- Case #03080875 - ST_GEOMETRY functions used in Select By Attributes tool on Shape field generate error "ORA-00904 Invalid Identifier "SHAPE"
- BUG-000150273
- https://community.esri.com/t5/arcgis-pro-questions/find-multi-part-features-using-sql-st-geometry/m-...

But no, the query looks normal.

SELECT OBJECTID FROM species_records WHERE CAST(t_date AS CHAR(20)) IS NOT NULL

I tried using that exact SQL to create a FGDB database view. But it failed, just like previous attempts to use CAST in the WHERE clause of a FGDB database view.

I also tried adding an extra column to the SELECT clause, just in case it didn't like only selecting the ObjectID. But that didn't work either.

SELECT OBJECTID, t_species FROM species_records WHERE CAST(t_date AS CHAR(20)) IS NOT NULL

ERROR 160195: An invalid SQL statement was used.

Of course, I don't have this problem in mobile or enterprise GDB database views.

Bud

Surprisingly, wrapping the WHERE clause CAST in a subquery seems to work. I'm able to create the view.

select
    *
from 
    species_records 
where
    objectid in (
        select
            objectid
        from
            species_records
        where
            cast(t_date as char(20)) is not null
        )

Bud

Esri Case #03527950 - Article 000008936 Select minimum and maximum values in the Select By Attributes window in ArcMap — Example SQL is incorrect (submitted Jan 12, 2024)

As this documentation is retired the Esri Inc will not make changes to the documentation as this relates to currently unsupported versions which may reference retired coding workflows.

I did complete testing using the suggested arcade expression within the documentation and did have the expression provide inconsistent results. The expression did work correctly in a few scenarios.

Bud

Esri Support:

...we will not be able to move forward with this case as the documentation you have highlighted is for no longer supported versions of the software and therefore will not be updated.

Bud

jlgprime

The generalized greatest-n-per-group solution worked for me after copying my table to a mobile geodatabase, thank you @JoshuaBixby !

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.