Select first row in each GROUP BY group with Postgres aggregate functions?

Question

I have two tables: user to keep track of user info and users_classes to keep track of relations between users and classes (classes are in a separate table). I want to join the tables and return the user's info along with an array of the classes they're in. Is there an efficient way to do so?

Table structure:

CREATE TABLE users (
  id        BIGSERIAL PRIMARY KEY,
  username  VARCHAR(255) UNIQUE,
  email     VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE users_classes (
  user_id   BIGINT REFERENCES users(id),
  class_id  BIGINT REFERENCES classes(id),
  PRIMARY KEY(user_id, class_id)
)

My attempt:

SELECT
  u.id,
  FIRST(u.username) AS username,
  FIRST(u.email) AS email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
JOIN users AS u
ON c.user_id = u.id
GROUP BY u.id;

I think this could work (after implementing FIRST()), but is there a more standard way to do it?

Answer 1

Just ad the columns to the GROUP BY clause like this:

SELECT
  u.id,
  u.username,
  u.email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
  JOIN users AS u
    ON c.user_id = u.id
GROUP BY u.id, u.username, u.email;

Answer 2

Postgres requires you specify how to pick up rows projection in group, so you have to specify an aggregation function for each select projection.

You could use mode() to pick first/last row in the group. Here is the example based on your question:

SELECT
  mode() WITHIN GROUP (ORDER BY u.id) as userId,
  mode() WITHIN GROUP (ORDER BY u.username DESC) as username, -- pick last username
  mode() WITHIN GROUP (ORDER BY u.email) as email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
JOIN users AS u
ON c.user_id = u.id
GROUP BY u.id;