How to remove duplicates, which are generated with array_agg postgres function

Question

Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user).

The query

SELECT array_to_string(array_agg(CONCAT(u.firstname, ' ', u.lastname)), ', ')
FROM log_has_item logitem
  INNER JOIN log log ON log.id = logitem.log_id
  INNER JOIN worker u ON log.worker_id = u.id
WHERE logitem.company_id = 1

Executable query is avaiable on sqlfiddle.com. Click on Run SQL button and you will result, which contains Frantisek Smith twice

In postgre 9.0+ there is string_agg(text,text) function. It may be easier to write string_agg(CONCAT(...),', ') — Ihor Romanchenko
– Ihor Romanchenko, Commented Oct 14, 2014 at 15:30
@a_horse_with_no_name distinct works fine, but scrambles the order. Any way to preserve it? — Rodrigo
– Rodrigo, Commented Mar 3, 2016 at 13:57
@a_horse_with_no_name I tried string_agg(distinct nome order by l.id,', ') and string_agg(distinct nome order by nome,', '), but both give "function string_agg(character varying) does not exist". I'll ask a question with the full sql to show you. — Rodrigo
– Rodrigo, Commented Mar 3, 2016 at 14:00

Mureinik · Accepted Answer · 2014-10-14 14:52:17Z

188

You can use the distinct keyword inside array_agg:

SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT CONCAT(u.firstname, ' ', u.lastname)), ', ')
FROM log_has_item logitem
  INNER JOIN log log ON log.id = logitem.log_id
  INNER JOIN worker u ON log.worker_id = u.id
WHERE logitem.company_id = 1

SQLFiddle with this example

answered Oct 14, 2014 at 14:52

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

4 Comments

Rodrigo Over a year ago

Any way to preserve the order using distinct?

P01550n Over a year ago

ERROR: DISTINCT is not implemented for window functions SQL State :0A000 (PostgreSQL 9.5.5)

thalisk Over a year ago

Saved me from having to write my own aggregate function!

Vérace Over a year ago

Wouldn't STRING_AGG be better? See my answer.

Vérace · Accepted Answer · 2021-11-01 09:26:32Z

6

No need to go all round the houses with ARRAY_TO_STRING(ARRAY_AGG( when a simple STRING_AGG will do as follows (code available on the fiddle here):

--
-- Simplified
--

SELECT
  DISTINCT
  STRING_AGG
  (
    DISTINCT CONCAT(w.firstname, ' ', w.lastname), ', '
  ) AS "The workers"
FROM log_item li
INNER JOIN log l ON li.log_id = l.id 
INNER JOIN worker w ON l.worker_id = w.id
WHERE li.company_id = 1;

Result:

                The workers
Frantisek Smith, Peter Duff

answered Nov 1, 2021 at 9:26

Vérace

1,00412 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to remove duplicates, which are generated with array_agg postgres function

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related