Search Postgresql Archives

Re: STRING_AGG and GROUP BY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Mar 16, 2018 at 7:17 AM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '') AS tiles,

Because for example in the last move with mid=6 the player Bob had played 2 tiles, both with letter-value "P" and has formed 2 words (PP and PABCD), but adding distinct would suggest he played a single tile "P".

​First reaction is to ARRAY_AGG(DISTINCT x) and then write a function that converts ​that array into a string by extracting 'letter' from each cell in the array.

Thinking it over a bit you have two columns that both are aggregates but that are otherwise independent of each other.  Since they are independent they cannot be aggregated at the same time.  You need to write a two subqueries, either in the target list or as separate from/join items, and then join the already aggregated queries together on their common group by column.

The presence of DISTINCT here (and, IMO, generally), even if it worked, would be an indicator that something is not quite right.

David J.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux