Search code examples
sqlarrayspostgresqlaggregate-functions

Exclude max element from each row


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}


Solution

  • 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.