CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
dzjin
dzjin

Reputation: 67

array_agg on multiple columns by one column

I currently have an example tagging table like this:

id | tag    | user_id | video_id
 1 | cake   |       1 |        1
 2 | cookie |       1 |        2
 3 | crab   |       2 |        2
 4 | carrot |       2 |        2

I'd like to group the most recent 10-20 rows by the video_id, with the other columns as arrays:

video_id 1, has tag "cake", user_id 1
video_id 2, has tags "cookie", "cake", "crab", by user_id 1, user_id 2

Upvotes: 1

Views: 2471

Answers (1)

James Mason
James Mason

Reputation: 4296

Something like this should work

SELECT video_id, array_agg(tag) as tags, array_agg(user_id) as user_ids
FROM tags
ORDER BY id
GROUP BY video_id
LIMIT 10

Upvotes: 2

Related Questions