topic Re: Select maximum values in Select By Attributes (greatest n per group) in Geodatabase Questions

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

Bud — Sat, 13 Jan 2024 01:15:03 GMT

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.

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

JoshuaBixby — Fri, 12 Jan 2024 15:31:20 GMT

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.

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

DavidPike — Fri, 12 Jan 2024 17:20:25 GMT

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.

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

Bud — Fri, 12 Jan 2024 18:57:45 GMT

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.

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

Bud — Mon, 15 Jan 2024 07:05:13 GMT

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.

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

JoshuaBixby — Sat, 13 Jan 2024 23:16:58 GMT

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.

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

Bud — Sun, 14 Jan 2024 00:24:29 GMT

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?

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

Bud — Mon, 15 Jan 2024 00:00:58 GMT

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

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

Bud — Sun, 14 Jan 2024 03:52:50 GMT

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
                )

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

JoshuaBixby — Sun, 14 Jan 2024 15:50:49 GMT

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 )

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

Bud — Mon, 15 Jan 2024 15:32:18 GMT

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.

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

Bud — Sun, 14 Jan 2024 21:25:39 GMT

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

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

JoshuaBixby — Mon, 15 Jan 2024 15:17:51 GMT

It is supported in Postgres, it is called a row constructor on the left-hand side instead of row value, but the same syntax works for both:

https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-IN

row_constructor IN (subquery)
The left-hand side of this form of IN is a row constructor, as described in Section 4.2.13

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

Bud — Tue, 16 Jan 2024 17:36:36 GMT

Do you figure it's logically impossible to get the greatest 1 per group (ties or no ties) in a FGDB SQL expression using current FGDB SQL functionality?

Since none of the following are supported:

Correlated subqueries
A row value on the left-hand side of the IN operator

Concatenating a string and a date into a poor man's multi-field join:

--works in normal databases, but not file geodatabases, due to limited concatenation functionality
t_species||' '||t_date in (
    select
        t_species||' '||max(t_date)
    from
        species_records
    group by
        t_species
)

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

JoshuaBixby — Tue, 16 Jan 2024 17:22:02 GMT

I wouldn't say it is logically impossible but technically impossible because of Esri's SQL implementation.

Concatenating text with a date is possible, in certain places, with file geodatabases, but the date must be CAST to text before concatenating: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

CAST function
The CAST() function converts a value or an expression from one data type to another specified data type. The syntax is as follows:
CAST (expression AS data_type(length))

When casting a date to text in a file geodatabase, the outputted date format is:

File geodatabases support the use of a time in the date field, so this can be added to the expression:
Datefield = timestamp 'yyyy-mm-dd hh:mm:ss'

So, 2/2/2021 3:00 PM casted to text is '2021-02-02 15:00:00'.

Although dates can be cast to text and concatenated with additional text, e.g., a species name, it apparently isn't allowed within a subquery, so that takes it off the table in this case. The following SQL works as a file geodatabase view

SELECT MAX(t_species || CAST(t_date AS CHAR(20))) FROM species_records GROUP BY t_species

but embedding that same SQL into a subquery (even a subquery in another file geodatabase view) generates an error. Note: The MAX function needs to be applied to the concatenated result and not just the date field.

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

Bud — Tue, 16 Jan 2024 21:14:16 GMT

Interesting.

I couldn't even get CAST() to work on its own in the WHERE clause in a simple view.

select
    *
from
    species_records
where
    cast(t_date as char(50)) is not null

ERROR 160195: An invalid SQL statement was used.

But that query works fine in Oracle and in a mobile geodatabase.

I'll report it to support as a FGDB bug.

Side note:

I left some feedback recently on the FGDB SQL page: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-file-gdb/sql-reporting-and-anlysis-file-geodatabases.htm

Esri wrote back today and said, "The page you reference has been mostly rewritten for the upcoming release, so you will see many changes in it later this year..."

Fingers crossed those docs have been improved.

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

JoshuaBixby — Tue, 16 Jan 2024 21:15:52 GMT

Rewriting the documentation and rewriting the SQL support are very different, and I suspect they only mean the former and not the latter. That said, the documentation can definitely be polished up.

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

JoshuaBixby — Tue, 16 Jan 2024 21:29:29 GMT

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

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

Bud — Fri, 19 Jan 2024 12:08:45 GMT

Esri Case 03529800 - FGDB database view: Can't use CAST function in WHERE clause

BUG-000164429: When creating a database view, using a cast function in the where clause results in an error

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

Bud — Sun, 21 Jan 2024 08:50:49 GMT

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.