Question 88875 in PostgreSQL Question posted in
The official documentation can be found here.

Exclude max element from each row – Postgresql

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}

Tags:

3

Answers


  1. 0 
    select array_agg(id) as ids
from fff
WHERE ROW_NUMBER() OVER (PARTITION BY a_c_id, a_c_dttm, a_c_code ORDER BY ID DESC) <> 1 
group by a_c_id, a_c_dttm, a_c_code
having COUNT(*) =>1

    You may need to move the ROW_NUMBER to a sub-query, some platforms don’t allow it in the WHERE statement.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    Use the max window function to filter out max id values for each group, so you may modify your query as the following:

    select array_agg(id) as ids
from 
(
  select *,
    max(id) over (partition by a_c_id, a_c_dttm, a_c_code) maxid
  from table_name
) t
where id <> maxid
group by a_c_id, a_c_dttm, a_c_code

    Here, you don’t need the having COUNT(*)>1 since one value (max value) is filtered out so the returned groups contain more than one row.

    And to get all of the ids aggregated in a single row just remove the group by clause.

    see demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search