Skip to content

Is there an alternative to WHERE COUNT () with Window Function in PostgreSQL?

I’m trying to do a grouping by two columns and filter the result only by records that don’t have the first column duplicated. Then the result values could be used as KEY and VALUE, respectively.
I achieved the desired result in two different ways, but none of them seems to be adequate.

To simplify the problem, I will summarize it with a table with only two columns and few values:

The first way was using CTE and WITH clause, then filtering with a subquery in the WHERE clause:

The second way was using a subquery with the OVER window function in the FROM clause:

Both ways return the same result, correct for the inputs used:

But they seem to be excessively expensive, besides not being so pleasant to read.

Is there a better way to achieve the same result?

Advertisement

Answer

This seems to be a simple group by with counting distinct values:

Online example

User contributions licensed under: CC BY-SA
10 People found this is helpful