Filter a layer in ArcGIS Online to only show Unique Values

Question

Hi All,

I've been working on this one for a bit and cant seem to find a solution. I would like to filter a layer to only show features which contain a unique value for a specific field.

For instance, if i have a field called "city" that contains 5 features with values, Seattle, Miami, Asheville, Miami, San Diego; I would expect the filtered layer to only have 3 features remaining, removing Miami because it is not unique. Is there a way to do this?

jcarlson · Answer

With the right SQL expression, sure! We use PostgreSQL, so I don't know if this translates to whatever RDBMS you might be on, but it might look like this:

SELECT DISTINCT ON (city_name)
    city_name, other_field, geometry
FROM cities
ORDER BY some_date_field DESC

But perhaps I'm misunderstanding your post. You mention you would expect only three results in your example, so cities which show up more than once should be omitted entirely? In that case, maybe:

WITH unique_cities AS (
    SELECT
        COUNT(objectid) AS the_count, city_name
    FROM cities
    GROUP BY city_name
)

SELECT
    city_name, other_field, geometry
FROM cities b
INNER JOIN a ON a.city_name = b.city_name
WHERE a.the_count = 1

BenjaminMittler1 · Answer

Publishing as a query layer, would that still allow the filter to be dynamic? meaning as new features are added with the same city they are filtered out?

jcarlson · Answer

If it were coming out of an enterprise geodatabase, you could publish it as a Query Layer, but that's about the only other option.