SQL MAX Subquery in arcpy.MakeFeatureLayer_management

Question

I am having an issue with subsequent subqueries in making a feature layer in arcpy. My table has fields: OBJECTID, pointid (long), grid_code (double)

I can successfully extract MAX(grid_code), however it returns multiple values and as such I only need the row from selection where MIN(pointid) exists.

I have not been successful in using MakeFeatureLayer and then SelectByAttribute after that as arcpy effectively concatenates the two queries with AND, where I need to select using IN (I believe).

My code as currently stands below:

arcpy.MakeFeatureLayer_management(outpoint, "centroidpoint", "grid_code IN (SELECT MAX(grid_code) FROM point WHERE (SELECT MIN (pointid) FROM point))")

outpoint is a point feature class in a geodatabase that is the result of a raster to point conversion.

Error is:

ExecuteError: ERROR 000358: Invalid expression grid_code IN (SELECT MAX(grid_code) FROM point WHERE (SELECT MAX (pointid) FROM point)) Failed to execute (MakeFeatureLayer).

I believe error is in query, however I haven't found if I can do nested / subqueries of Max/Min funcitons.

Answer 1

An sql where clause can't incorporate sql functionality such as MAX or MIN because your input can only be what proceeds WHERE in an sql. Note how in the Select By Attribute window the first part of the SQL query is already provided:

The make feature layer sql where clause behaves in the same fashion. If you want to create a feature layer with a minimum or maximum value, you must first determine what that value is with prior python code. I like the data access search cursor for this because it does allow for a more robust sql clause.

Finding the maximum oid in a feature class and creating an SQL where clause with it:

with arcpy.da.SearchCursor ("BlowOffFc", "OID@",
                            sql_clause =
                            (None,
                             "ORDER BY OBJECTID DESC")) as cursor:
    for maxOid, in cursor:
        break
del cursor
sql = "OBJECTID = {}".format (maxOid)
arcpy.MakeFeatureLayer_management ("BlowOffFc", "BlowOffFc_Max", sql)

Answer 2

First, File Geodatabases (limited) and enterprise geodatabases (full) are the only types of geodatabases that support subqueries like you are doing. See here for examples: http://pro.arcgis.com/en/pro-app/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

You might begin with a more simple query and build up from there. Or try it in the Select by Attributes layer to see if it works before plugging it into the code.

arcpy.MakeFeatureLayer_management(outpoint, "centroidpoint", "grid_code IN (SELECT MAX(grid_code) FROM point WHERE (SELECT MIN (pointid) FROM point))")

The two main problems with this statement that I can see is the FROM is looking for a point layer or feature class and you are using something called outpoint to create the layer (two different layers). You might need to create a feature layer for point to load it into memory, or make sure the env.workspace is set properly. Other than that, this portion should work in theory:

"grid_code IN (SELECT MAX(grid_code) FROM point"

I tested something similar on my version of 10.2 using a layer for the second feature class.

This section also needs work:

WHERE (SELECT MIN (pointid) FROM point))

A where statement would also need an = or > or < or like. Perhaps it should be:

WHERE pointid = (SELECT MIN (pointid) FROM point))

I would start with just getting the first part working then add in the second where statement.

Edit:

Also if you are getting multiple gridcodes back you could add the DISTINCT keyword in front.

grid_code IN (SELECT DISTINCT MAX(grid_code) FROM point)