3

I have a RoadInsp FC in a FGDB in ArcGIS Pro 2.6.8.

enter image description here

Using an SQL expression in the Select by Attributes tool, I want to select the greatest 1 per group.

In other words, for a given ASSET_ID, I want to select the inspection that has the latest DATE_. The selections in the screenshot reflect what I'm looking for.

Use case: One-to-first joins: Control what related record is used


I've written a SQL subquery that works as expected on a copy of the table in an Oracle 18c 10.7.1 EGDB:

objectid in (
    select      a.objectid
    from        roadinsp a
    inner join
       (select  asset_id, max(date_) as date_
       from     roadinsp
       group by asset_id) b
    on a.asset_id = b.asset_id and a.date_ = b.date_
    )

enter image description here

But when I try to use that same expression on the FGDB FC, I get an error:

enter image description here

ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

Question:

Why do I get that error when I use the expression on the FGDB FC?

I've looked at the FGDB SQL syntax in the docs:

But as far as I can tell, all of the SQL syntax I've used is supported by the ArcGIS SQL dialect and FGDBs.

I've tested the same expression in ArcGIS Pro 3.0.3 and I get the error there too.


Related:

Select greatest n per group using EXISTS


Edit:

One thing I didn’t consider:

If an asset has multiple rows with the same top date, then only one of those rows should be selected.

10
  • 2
    The only thing worse than Esri's SQL support with file geodatabases is their documentation of it. The issue you are running into is related to the correlated subquery issue discussed on Esri Community: When EXISTS Doesn't: File Geodatabases and Correlated Subqueries
    – bixb0012
    Dec 6, 2022 at 18:01
  • 1
    The SQL syntax you've used isn't supported, certainly by Pro 2.6, as the "Invalid expression" error indicates. Keep in mind that SQL support isn't implemented by a full SQL Engine, so this isn't an exhaustively tested feature. Functionality is added as identified, but an inner join on a virtual table is not a SQL92 feature, and certainly a bridge too far for the current FGDB SQL implementation.
    – Vince
    Dec 6, 2022 at 19:03
  • @Vince The SQL for reporting and analysis on file geodatabases (2.6) docs say that INNER JOIN is supported. And subqueries are supported as well: SQL reference for query expressions used in ArcGIS (2.6).
    – User1974
    Dec 7, 2022 at 15:51
  • I would think that if Esri implemented the JOIN SQL syntax for reporting in FGDBs, then the syntax would work for SQL expressions too. I can't imagine there's logic in the FGDB engine that says, "JOIN only works for SQL that's for a report, but not for SQL that's for an expression". I don't think that'd be possible, so I have to assume that the reporting syntax works for SQL expressions too. I think my SQL should work.
    – User1974
    Dec 7, 2022 at 15:51
  • 1
    @User1974, that one isn't mine; however, I have logged numerous defects and enhancements over the years relating to SQL support in file geodatabases. Looking back at bugs/enhancements I have logged with similar numbers, that specific ENH is likely from the end of 2018. If that ENH hasn't been acted on by now, I would not expect it ever will.
    – bixb0012
    Dec 7, 2022 at 19:28

2 Answers 2

1

As you have worked out the SQL used in ArcGIS Pro is different to what is used in database systems, the key issue I think being this statement in the help file

Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in an SQL expression in ArcGIS except when using subqueries.

So one has to come up with a work around; one such solution I have employed is this:

Run a summary stats tool, this allows you to group data, my demo is shown below:

Sample

Run the Add join, join by Asset_ID

Add Join

Now select where date is equal to max date Select

Finally if required, remove join

This approach could be very easily turned into a model tool rather than you running the individual tools. The alternative is to use cursors in a Python script.

2
0

Here's a FGDB database view that works on a similar table called species_records.

--species_records_latest_vw
select
    *
from
    species_records
inner join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on species_records.t_species = l.t_species 
       and species_records.t_date = l.t_date

Unfortunately, I couldn't get it to work as a SQL expression. I guess because it is a correlated subquery, just like my query in the original post above. However, a database view might be useful as an alternative.

Also, the query doesn't break ties when there are multiple rows per species with the same date.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.