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

537
5
06-22-2017 09:54 AM
Highlighted
New Contributor II

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?

Reply
0 Kudos
5 Replies
Highlighted
by
Occasional Contributor III

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

Reply
0 Kudos
Highlighted
New Contributor II

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.

Reply
0 Kudos
Highlighted
by
Occasional Contributor III

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) 

Highlighted
by
Occasional Contributor III

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.  

Highlighted
New Contributor II

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

Reply
0 Kudos