I wrote sql
select array_agg(id) as ids
from fff
group by a_c_id, a_c_dttm, a_c_code
having COUNT(*)>1
which returns results
{5,9}
{8,12,13}
{6,10}
{7,11}
How to exclude max value in each row for getting this result?
{5}
{8,12}
{6}
{7}
the end goal is to get the following result: {5,8,12,6,7}
Aggregate in one table all ids
except max in groups:
select array_agg(id) as ids
from my_table
where id not in (
select max(id)
from my_table
group by other_col
);
Note that one-element groups are also excluded, so you do not have to treat them separately.
Test it in db<>fiddle.