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.
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?
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.
This should help
Pass this definition to outstatistics. Here is the linke if you additional information
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.
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.