How to get the top X results of a given category using Ecto?

Questions / Help
9 


    Repo.insert_all(ExampleRecord, [
      [name: "John", value: 20],
      [name: "John", value: 7],
      [name: "John", value: 7],
      [name: "John", value: 7],
      [name: "John", value: 4]
    ])

    table =
      Ecto.Query.from(t in ExampleRecord,
        select: %{
          rn: over(row_number(), partition_by: [t.name], order_by: [desc: t.value]),
          name: t.name,
          value: t.value
        }
      )

    Ecto.Query.from(r in Ecto.Query.subquery(table), where: r.rn <= 3, order_by:  r.name)
    |> Repo.all()

The :partition_by option of over() can be a list of fields, if that helps.

2 Likes