Filter a layer in ArcGIS Online to only show Unique Values

[BenjaminMittler1]

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]

It depends on where you want to do this. If you're wanting to apply this filter in a map, you can't.  Map filters only let you work with simple SQL expressions, nothing dynamic, and no other SQL functions.

You could do this in certain Dashboard widgets, or by using a Data Expression in a Dashboard, but I don't think you can do this anywhere else.

[BenjaminMittler1]

Ha, thanks. I just saw you answered an almost identical question just a few hours ago. I'm trying to have this query make its way into an experience builder, but it looks like that's not going to happen. 

[jcarlson]

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.

[BenjaminMittler1]

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]

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