Query Feature Service and Rest API

Question

I am trying to get the max(date) from a feature service using the rest api.

The where clause I am using is

SellDate =(select max(SellDate) from Buncombe_County_Property_Sales_Data)

But I keep getting 'where' parameter is invalid.

https://services6.arcgis.com/VLA0ImJ33zhtGEaP/ArcGIS/rest/services/Buncombe_County_Property_Sales_Data/FeatureServer/0/q…

Thanks

Vicki

RandyBurton · Answer

Are you using AGOL or your own server/service? Are you using an HTML form, or are you using another method for your query? In the meantime, I would suggest the following: "where" : "1=1", "outFields" : "*", "orderByFields" : "SellDate DESC", "resultRecordCount" : "1"‍‍‍‍‍‍‍‍

JakeSkinner · Answer

Hi Vicki, ArcGIS Online only allows certain standardized queries: Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS Subqueries as a where clause, for example, POP_2010 = (SELECT min(POP_2010) FROM counties, are not supported You can accomplish what you're looking for Python, though. Here is an example: import urllib, urllib2, json, datetime, time queryURL = 'https://services6.arcgis.com/VLA0ImJ33zhtGEaP/ArcGIS/rest/services/Buncombe_County_Property_Sales_Data/FeatureServer/0/query' params = {'f': 'json', 'where': '1=1', 'outFields': '*', 'returnIdsOnly': 'true'} req = urllib2.Request(queryURL, urllib.urlencode(params)) response = urllib2.urlopen(req) ids = json.load(response) minId = ids['objectIds'][0] maxId = ids['objectIds'][-1] x = minId while x < maxId: y = x + 1000 if y > maxId: y == maxId + 1 where = 'FID >= ' + str(x) + ' AND FID < ' + str(y) params = {'f': 'json', 'where': where, 'outFields': '*'} req = urllib2.Request(queryURL, urllib.urlencode(params)) response = urllib2.urlopen(req) data = json.load(response) dateList = [] for data in data['features']: dateList.append(data['attributes']['SellDate']) x += 1000 y += 1000 dateList.sort() maxDate = str(dateList[-1])[0:10] print(time.strftime(datetime.datetime.fromtimestamp(int(maxDate)).strftime('%c')))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍