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?
Advertisement
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;