jhhayashi jhhayashi - 23 days ago 9
SQL Question

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

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

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;