How to remove duplicates, which are generated with array_agg postgres function
SqlPostgresqlSelectArray AggSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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