CODEWITHSUNDEEP
sqlpostgresql
Dennis
Dennis

Reputation: 1975

Ordering DISTINCT with string_agg using another column on PostgreSQL 13?

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

Upvotes: 0

Views: 770

Answers (2)

S-Man
S-Man

Reputation: 23676

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

Upvotes: 1

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions