How to query only top 10 records?

[BrandonFlessner]

Is there a way to query a feature layer to return the highest ten values for a field with the JavaScript API? I’m developing an app and I’d like to just have the server return the needed records (for better performance), instead of returning several thousand and just using the top ten. Is this possible?

I've tried using the query.num property but that only works for hosted arcgis.com services. I've also looked into enabling pagination on the map service so that I can specify a query.resultRecordCount property, but it looks like that won’t be possible (https://community.esri.com/thread/118631) with a file geodatabase back end. Also no support for a SELECT TOP … SQL query on file geodatabases. I could limit the "Maximum Number of Records Returned by Server" on the service properties, but this would require a whole new service because I need to return all records for display on the map (Not an elegant solution).

var topTenQuery = new Query();
topTenQuery.returnGeometry = false;
//topTenQuery.where = "ACCRATE > 0 limit 10"; // causes error
//topTenQuery.where = "ACCRATE IN (SELECT TOP 10 ACCRATE FROM [featureLayer])"; // causes error
topTenQuery.outFields = ['OBJECTID', 'RDNAME', 'ACCRATE'];
topTenQuery.orderByFields = ['ACCRATE DESC'];
//topTenQuery.resultRecordCount = 10; //causes error because pagination is not enabled
featureLayer.queryFeatures(topTenQuery, resultsHandler);

Thanks for any help!

[thejuskambi]

The Query object has property num which takes in values for the number of records to be returned. It should be used alson with start. check the api reference for Query object.

[BrandonFlessner]

This also causes an error. From firebug: "Error: Pagination is not supported."

[ChrisSmith7]

Brandon, it sounds like you've done your DD - I was going to recommend capping the service if nothing else worked. This seems like a really basic feature that should be supported.

[BrandonFlessner]

Thanks for the responses Chris and Thejus. It looks like this is something that is being implemented, it just hasn't quite trickled down far enough. Hopefully this feature will be supported in future upgrades to the API or ArcGIS for Server.

[thejuskambi]

I check the implementation for 3.13 api and there is no reference of start or num. My guess is good as yours, Its still being implemented. Wonder why they have it updated in the document.

[ChrisSmith7]

Not sure if this is helpful, but the API says you can use any valid WHERE clause SQL. So, that had me thinking... This is a really janky way of doing things, but you could technically do something like this in SQL Server to return top n:

select * from myTable where myField in (select top 10 myField from myTable where myValue = 'False'))

I came across this thread where others have used subqueries, so maybe it's possible this way:

arcsde - How can I use a sub-query in where statement of a query in ArcGIS 10.2 Map service? - Geogr...

[RobertScheitlin__GISP]

Chris,

   Unfortunately Brandon already covered this in the original post

Also no support for a SELECT TOP … SQL query on file geodatabases

[BrandonFlessner]

Looks like subqueries could be a work around if your back end SQL database supports something like 'top' or 'limit', but it also opens up some security vulnerabilities to SQL injection attacks. ArcGIS Help (10.2, 10.2.1, and 10.2.2) shows that 'top' isn't supported with the standard queries on ArcGIS Server. So not useful for me but could be work for folks with other environments. Thanks Chris

[ChrisSmith7]

That's a good point about increasing vulnerability to SQL injections. I found an updated page (your link is throwing a 404) indicating the supported functions:

Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS

I could see disabling this feature if the limitations were prohibitive and your application existed inside of a walled garden.