Ben Ben - 1 year ago 62
SQL Question

How to build a list of unique values and display an array of their ids?

This code is a simplified version of the concept I'm trying to achieve. In reality I'm working with the results of a somewhat complex query - but I figured this simple example would be enough.

I'm trying to output an ordered list of unique values with an array of their id's. I'm unsure if I need to use one or two select statement, or if I should use one select statement and some ruby code.

The data (people)

id: first: last:
1 Julie Brown
2 Julie Brown
3 Mike Smith
4 Julie Brown
5 Andrea Smith
6 Mike Smith
7 Jim Brown

Desired output

Jim Brown (7)
Julie Brown (1, 2, 4)
Andrea Smith (5)
Mike Smith (3, 6)

Answer Source
@people = Person
  .select(:first, :last, "array_agg(id) AS ids")
  .group(:first, :last)

You could use the array_agg function to get all the ids in a single query.

@people.each do |person|
  full_name = [person.first, person.last].join(" ")
  puts "#{ full_name } (#{ person.ids.join(", ") })"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download