Ordering DISTINCT with string_agg using another column on PostgreSQL 13?

Question

I have an emails table:

CREATE TABLE public.emails (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  name  text not null
);

I have a contacts table:

CREATE TABLE public.contacts (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  email_id            bigint NOT NULL,
  full_name            text NOT NULL,
  ordering  int  not null
);

And records as follow:

insert into emails (name) VALUES ('dennis1');
insert into emails (name) VALUES ('dennis2');

insert into contacts (id, email_id, full_name, ordering) VALUES (5, 1, 'dennis1', 9);
insert into contacts (id, email_id, full_name, ordering) VALUES (6, 2, 'dennis1', 5);
insert into contacts (id, email_id, full_name, ordering) VALUES (7, 2, 'dennis5', 1);
insert into contacts (id, email_id, full_name, ordering) VALUES (8, 1, 'john', 2);

My query to fetch data as follows:

SELECT
  "emails"."name",
  STRING_AGG(DISTINCT CAST("contacts"."id" AS TEXT), ','
  ORDER BY CAST("contacts"."id" AS TEXT)) AS "contact_ids"
FROM "emails"
  INNER JOIN "contacts"
    ON ("contacts"."email_id" = "emails"."id")
WHERE "emails"."id" > 0
GROUP BY "emails"."name"
ORDER BY "emails"."name" DESC LIMIT 50

Actual Result

name       contact_ids
dennis2    6,7
dennis1    5,8

Expected Result

name       contact_ids
dennis2    7,6
dennis1    8,5

I want to order contact_ids based on ordering column as DESC but i do not want to fetch ordering column. Just use it to order id of contacts.

How can I sort contact_ids's each id based on ordering column?

Demo: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=4d6851ec67b579608427bb399eae5891

Why do you want to leave out the ordering column if you have one? — S-Man
– S-Man, Commented Feb 26, 2021 at 9:52
Unrelated, but: ORDER BY CAST("contacts"."id" AS TEXT) is a bad idea, because that would sort 10 before 2 — user330315
– user330315, Commented Feb 26, 2021 at 9:54

score 3 · Accepted Answer · 2021-02-26 10:04:28Z

3

If you aggregate before joining, you don't need the DISTINCT and you are free to order by anything you want:

select em.name,
       c.contact_ids 
from emails em
  join (       
    select email_id, string_agg(id::text, ',' order by ordering desc) as contact_ids
    from contacts
    group by email_id
  ) c on c.email_id = em.id
order by em.name desc 
limit 50;

Online example

edited Feb 26, 2021 at 10:04

answered Feb 26, 2021 at 9:53

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Dennis Over a year ago

Thank you. Just one more thing. What If i want another column to be merged with comma? Lets say I want user_id field/column to be merged? In this case your result gives me duplicate user_id due to group. See here: dbfiddle.uk/…

user330315 Over a year ago

@Dennis: well, obviously you need to include that additional column in the derived table, otherwise you can't use it for the join.

S-Man · Accepted Answer · 2021-02-26 15:05:59Z

1

demo:db<>fiddle

I guess, leaving out the ordering column is because of the problem, that you cannot use it int the aggregation with DISTINCT.

So, possibly you can do the DISTINCT before aggregating:

SELECT
    "name",
    STRING_AGG(CAST("id" AS TEXT), ',' 
        ORDER BY "ordering") AS "contact_ids"
FROM (
  SELECT DISTINCT ON ("contacts"."id")
    "emails"."name",
    "contacts"."id",
    "contacts"."ordering"
  FROM "emails"
    INNER JOIN "contacts"
      ON ("contacts"."email_id" = "emails"."id")
  WHERE "emails"."id" > 0
  ORDER BY "contacts"."id"
) s
GROUP BY "name"
ORDER BY "name" DESC LIMIT 50

edited Feb 26, 2021 at 15:05

answered Feb 26, 2021 at 10:01

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Ordering DISTINCT with string_agg using another column on PostgreSQL 13?

Actual Result

Expected Result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Actual Result

Expected Result

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related