CODEWITHSUNDEEP
sqlpostgresql
Nick Humrich
Nick Humrich

Reputation: 15785

ORDER BY value in join table not grouped before aggregation

I am trying to order a Postgres result set based on an array_aggregate function.

I have the following query that works great:

select a.id, a.name, array_agg(f.name) 
from actors a 
join actor_films af on a.id = actor_id 
join films f on film_id = f.id 
group by a.id
order by a.id;

This gives me the following results, for example:

 id |  name  |            array_agg
----+--------+---------------------------------
  1 | bob    | {"delta force"}
  2 | joe    | {"delta force","the funny one"}
  3 | fred   | {"bad movie",AARRR}
  4 | sally  | {"the funny one"}
  5 | suzzy  | {"bad movie","delta force"}
  6 | jill   | {AARRR}
  7 | victor | {"the funny one"}

I want to sort the results so that it is sorted alphabetically by Film name. For example, the final order should be:

 id |  name  |            array_agg
----+--------+---------------------------------
  3 | fred   | {"bad movie",AARRR}
  6 | jill   | {AARRR}
  5 | suzzy  | {"bad movie","delta force"}
  1 | bob    | {"delta force"}
  2 | joe    | {"delta force","the funny one"}
  4 | sally  | {"the funny one"}
  7 | victor | {"the funny one"}

This is based on the alphabetical name of any movies they are in. When I add the ORDER BY f.name I get the following error:

ERROR: column "f.name" must appear in the GROUP BY clause or be used in an aggregate function

I cannot add it to the group, because I need it aggregated in the array, and I want to sort pre-aggregation, such that I can get the following order. Is this possible?

If you would like reproduce this example, here is the setup code:

create table actors(id serial primary key, name text);
create table films(id serial primary key, name text);
create table actor_films(actor_id int references actors (id), film_id int references film (id));
insert into actors (name) values('bob'), ('joe'), ('fred'), ('sally'), ('suzzy'), ('jill'), ('victor');
insert into films (name) values('AARRR'), ('the funny one'), ('bad movie'), ('delta force');
insert into actor_films(actor_id, film_id) values (2, 2), (7, 2), (4,2), (2, 4), (1, 4), (5, 4), (6, 1), (3, 1), (3, 3), (5, 3);

And the final query with the error:

select a.id, a.name, array_agg(f.name) 
 from actors a 
join actor_films af on a.id = actor_id 
join films f on film_id = f.id 
group by a.id 
order by f.name, a.id;

Upvotes: 2

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You can use an aggregation function:

order by min(f.name), a.id

Upvotes: 2

Related Questions