Search code examples
sqlsubqueryarcgisterminologyscalar-subquery

Is the SQL in brackets a scalar subquery?

I use GIS software that has a proprietary database called a File Geodatabase.

Their documentation https://pro.arcgis.com/en/pro-app/3.1/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm says:

SQL reference for query expressions used in ArcGIS

Subqueries

Subquery support in file geodatabases is limited to the following:

  • Scalar subqueries with comparison operators. A scalar subquery returns a single value, for example:

    GDP2006 > (SELECT MAX(GDP2005) FROM countries)

    For file geodatabases, the set functions AVG, COUNT, MIN, MAX, and SUM can only be used in scalar subqueries.

  • EXISTS predicate, for example:

    EXISTS (SELECT * FROM indep_countries WHERE COUNTRY_NAME = 'Mexico')

I have a query that I use to create a database view in a file geodatabase. The view runs without errors.

--species_records_latest_vw
--For each species group, select the row that has the latest date. 
--Does not break ties if there are multiple rows per species with the same date.

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

Sample Excel data: https://community.esri.com/ccqpr47374/attachments/ccqpr47374/arcgis-pro-ideas/27986/1/species_records.xlsx

Is the SQL in brackets a scalar subquery?

If it is, is the documentation incorrect?

Solution

  • It is not a scalar subquery. A scalar subquery only returns one column and one row, for one total value.

    This is called a derived table.