Postgres "group by" on aggregate function(s)

Question

I have a query that returns an array of IDs which I then sort into order using a custom function as follows:-

SELECT array_sort(my_array) as sort FROM table

This will return:-

{19,21,24,48}
{19,21,24}
{19,21}
{19}
{16,12,13}
{16,12}
...

I want to pick the longest array with distinct first elements, so from the list above I would get:-

{19,21,24,48} and {16,12,13}

How can I achieve this, I tried pulling out the first element as a separate item, sorting by length and attempting to group as follows:-

SELECT DISTINCT (array_sort(path))[1] as first, array_length(path,1) as plen, array_sort(path) as members FROM table GROUP BY first,plen,members ORDER BY plen DESC

This does not work and simply orders the list

Consider rephrasing. It's not clear at all what you're asking. — Denis de Bernardy
– Denis de Bernardy, Commented Dec 6, 2013 at 11:23

roman · Accepted Answer · 2013-12-06 11:42:32Z

1

use distinct on clause:

with cte as (
  select
      array_length(members,1) as plen,
      members[1] as first,
      members
  from (select array_sort(path) as members from table) as a
)
select distinct on (first)
    members
from cte
order by first, plen desc

sql fiddle example

answered Dec 6, 2013 at 11:42

roman

118k30 gold badges205 silver badges209 bronze badges

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

Collectives™ on Stack Overflow

Postgres "group by" on aggregate function(s)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related