topic Re: SQL DISTINCT in ArcGIS JavaScript Maps SDK Questions

SQL DISTINCT

GordonBooth — Tue, 15 Jan 2013 16:55:00 GMT

I???m working on a project to identify sexual offenders by their alias name(s). When I do a query on an alias name it displays the master name (given name) and location. However an offender with the master name of James may have multiple alias???s like: Jim, Jimmy and Jimmie. I want to remove the duplicate results and display the master name and location only once. Can I remove the duplicates with a SQL DISTINCT statement? Does anyone have an example of a SQL DISTINCT in a query.where? I???m using ArcGIS 10 Desktop and Server.
Thanks

Re: SQL DISTINCT

JustinPeters — Wed, 20 Feb 2013 12:23:04 GMT

I also have a need for a DISTINCT in a query.where anyone have suggestions?

Re: SQL DISTINCT

JustinPeters — Wed, 20 Feb 2013 13:32:02 GMT

Found a workaround since DISTINCT is not supported, from http://blogs.esri.com/esri/arcgis/2009/10/07/using-javascript-to-populate-a-combobox-with-unique-values/

//Loop through the QueryTask results and populate an array
        //with the unique values
        var features = results.features;
        dojo.forEach (features, function(feature) {
          zone = feature.attributes.ZONING_TYPE;
          if (!testVals[zone]) {
            testVals[zone] = true;
            values.push({name:zone});
          }
        });

Re: SQL DISTINCT

DianaBenedict — Wed, 20 Feb 2013 14:37:12 GMT

I have never tried using the esri.tasks.StatisticDefinition object but it looks like you might be able to use the statisticType = 'count' and then you could use the returned featureset to obtain your unique list based on the onStatisticField.

Once you have setup the StatisticsDefinition object then you can use it in your Query Object (esri.tasks.Query)

Query.outStatistics

However, note that there seems to be the following requirements:
1) Requires ArcGIS Server service version 10.1 or greater (As of v2.6)
2) outStatistics is only supported on layers/tables where supportsStatistics is true.
3) If outStatistics is specified the only other query parameters that will be used are groupByFieldsForStatistics, orderByFields, text, timeExtent and where.

What I do not know is if the StatisticsDefinition only permits statistics on integer fields or if you can use the statistics type = "count" for character fields.

Refer to the Help doc for additional info:
http://help.arcgis.com/en/webapi/javascript/arcgis/jsapi/#query

Re: SQL DISTINCT

ReneRubalcava — Wed, 20 Feb 2013 15:19:09 GMT

The REST API has a "returnDistinct" option for Query in 10.1 SP1, but I have not tried it yyet.
http://resources.arcgis.com/en/help/rest/apiref/query.html

Maybe you could add it to the query object, and try it. If that doesn't work you could experiment with just using esri.request() to do the query manually.

Re: SQL DISTINCT

BradBarnell — Fri, 22 Feb 2013 14:04:02 GMT

The return distinct option in 10.1 SP1 is not yet available in the js api.

Re: SQL DISTINCT

NianweiLiu — Mon, 04 Mar 2013 18:50:55 GMT

In ArcMap, File--add a query layer, then create a SQL query with DISTINCT.
Note although it says add a layer, you do not need to have spatial column. The query will be added as a TABLE.

Then run a where="1=1" on this table.