Is it possible to sort groups created by group_by with fragment

Questions / Help
,
1

Hey there,
I have this function that I pipe my query through:

  def group_by_reference_id_and_date(query) do
    from(n in query,
      group_by: [
        fragment("date_trunc(?, ?)", "day", n.inserted_at),
        n.reference_id,
        n.reference
      ],
      select: fragment("array_agg(DISTINCT ?)", n.id)
    )
  end

but I am not able to order the groups in any way, is that possible to do?
I would want to order it by n.inserted_at, taking the latest inserted_at from each group, so the groups would be in a descending chronological order.

1 Like
2

As per the documentation on array_agg, you can use ORDER BY clauses in it to sort them. :slight_smile:

I.E. something like array_agg(DISTINCT ? ORDER BY ?) or so (or add DESC after the last ? or whatever). :slight_smile:

1 Like
4

i accidentally deleted my answer, but here is what I ended up doing:

“array_agg(? || ', ’ || EXTRACT(EPOCH FROM (SELECT ? AT TIME ZONE ‘UTC’)) *1000 ORDER BY ? DESC)”

1 Like
5

Cool, figured it should be something like that! Thanks for the followup! :slight_smile:

1 Like