How do I get the nth record for each distinct name in a table in rails?

Question

Essentially I want to get the nth last record for each distinct name.

So if I have a table that looks something like

name    field1
a        1
b        3
a        4
a        2
c        1
c        0
b        3
b        2
c        1

And I wanted to query for the 2nd record of each distinct name (n=2) I would want to get the records

  name    field1
   a       4
   c       0
   b       3

Is there any way to do this in one query. Or do I need to query once for every distinct name?

I have been attempting to use the group function, but could not get it to return anything other than the last record in for each group.

Answer 1

You can use order to get your records ordered by their name attribute:

# => [#<model:0x007f8293e61820 id: 1, name: "a", field1: "1"...,
 ...
 #<model:0x007f8293e60150 id: 9, name: "c", field1: "1"...]

Then use pluck to get only the name and field1 attribute:

# => [["a", "1"], ["a", "4"], ["a", "2"], ["b", "3"], ["b", "3"], ["b", "2"], ["c", "1"], ["c", "0"], ["c", "1"]]

With that you can work over your result as a hash and use group_by to group them by their first element:

# => {"a"=>[["a", "1"], ["a", "4"], ["a", "2"]], "b"=>[["b", "3"], ["b", "3"], ["b", "2"]], "c"=>[["c", "1"], ["c", "0"], ["c", "1"]]}

And then use map to get the second array value for every key value in the main hash:

# => [["a", "4"], ["b", "3"], ["c", "0"]]

So you could try with:

Model
  .order(:name)
  .pluck(:name, :field1)
  .group_by(&:first)
  .map{|_,v| v[1]}
  .to_h
# => {"a"=>"4", "b"=>"3", "c"=>"0"}