topic Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column in ArcGIS JavaScript Maps SDK Questions

Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

DannyVenier — Thu, 22 Jun 2017 16:54:09 GMT

I'm familiar with the QueryTask and Query where I can specify a WHERE clause where the layer URL is baked into the QueryTask and thus points to the table backing the layer map service. In that case, I'm limited to specifying only the WHERE clause of a traditional SQL query.

In this case I want to get a maximum from a column so I would need to specify a full SQL query (or a subquery to the WHERE clause) to contain SELECT MAX("date_time"). I see with ArcMap, there is a full SQL API to query but nothing equivalent for a javascript client using the SDK (3.2)?

Is it possible to specify something like myQuery.where = "EXISTS (SELECT MAX(date_time))" , then execute the query within the QueryTask pointing to the layer Url ?

If this is not possible, is there any other way to do this, perhaps using statistics?

Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

sapnas — Thu, 22 Jun 2017 17:47:21 GMT

Adding Order By Fields: date_time desc and retrieving the very first row will return MAX(date_time)

Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

DannyVenier — Thu, 22 Jun 2017 18:18:59 GMT

Thanks for the suggestion. Is it possible to add the Order By clause to a WHERE clause? The issue is that there is perhaps 1 million records for which I need the maximum (most recent) time. But I don't want to retrieve a million records because of the time/resources needed. So I was hoping to have the processing done on the server side and just return the scalar result. What you're suggesting would require a WHERE clause that generates a sorted (temporary) result on the server from which another query could grab the first row. The real issue seems to be that the client has no means to form and execute a proper SQL query. We are limited to a WHERE clause which is then (probably) massaged by the SDK into a full SQL query by adding the outfields part and the FROM table part. I'm thinking perhaps the statistics APIs might allow for a scalar function against the DB/table but have not experimented with those APIs.

Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

sapnas — Thu, 22 Jun 2017 18:30:37 GMT

This should help

[{
"statisticType": "max",
"onStatisticField": "date_time",
"outStatisticFieldName": "date_time"
}]

Pass this definition to outstatistics. Here is the linke if you additional information

Query - Feature Service (Operation)

Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

sapnas — Thu, 22 Jun 2017 18:49:36 GMT

Normally when I have a very complex query I would either end up creating a spatial view and publishing that as a map service Or use my custom server side code and build queries dynamically.

Re: Is it possible to do a feature Query from javascript client to obtain the MAX value for attribute/column

DannyVenier — Thu, 22 Jun 2017 19:17:04 GMT

Much thanks Sapna, that did the trick. I have one other issue in that some columns were published with dots in the name (like DB.DATE_TIME) and the dot notation seems to be unwelcome, but when I try your stats query on a column without the dot character it works great. Will probably need to get the data published in a friendlier form.

thanks again for taking the time to help.

--Danny