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:

create table example (
    foreign_key integer,
    item_value text
);

insert into example (foreign_key, item_value) values 
(1, 'a'), (1, 'a'), (1, 'b'), (1, 'a'), (2, 'a'), (2, 'a'), (2, 'a'), 
(3, 'c'), (3, 'a'), (3, 'a'), (4, 'a'), (4, 'c'), (4, 'e'), (5, 'b');

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

with grouped AS (
    select foreign_key, item_value 
    from example 
    group by 1, 2 
    order by 1 -- ordering only to view in case of running individually.
)
select * 
from grouped g 
where (select count(foreign_key) from grouped where foreign_key = g.foreign_key) = 1;

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

select foreign_key, item_value 
from (
    select *, count(foreign_key) over(partition by foreign_key) as n 
    from example 
    group by 1, 2 
) t 
where t.n = 1;

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

foreign_key item_value
2   "a"
5   "b"

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

Is there a better way to achieve the same result?

Answer

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

select foreign_key, max(item_value) as item_value
from example
group by foreign_key
having count(distinct item_value) = 1
order by foreign_key;

Online example

Advertisement

Answer