How to get associated records through the max attribute

In a Rails app with a classic has_many through association

class User < ApplicationRecord
  has_many :job_descriptions
  has_many :roles, through: :job_descriptions
end

class Role < ApplicationRecord
  has_many :job_descriptions
  has_many :users, through: :job_descriptions
end

class JobDescription < ApplicationRecord
  belongs_to :role
  belongs_to :user
end

How can I retrieve, for a user, only the roles associated with the job description with the maximum role version?

For instance, with the following data in the job_descriptions table.

I should retrieve the job descriptions with IDs 2 and 5 for the user with ID 1.

There are likely other suitable database specific and agnostic queries these are just the first ones that came to mind. I make no performance guarantees, specifically regarding the second option but if anyone would like to comment with additional (preferably better performing) options I would be more than willing to translate them to Arel.

General Usage

JobDescription.max_role_versions.where(user_id: 1)

PostgreSQL: using DISTINCT ON

class JobDescription < ApplicationRecord
  scope :max_role_versions, -> {
    job_descriptions = arel_table
    sub_query = job_descriptions
       .project(job_descriptions[Arel.star])
       .distinct_on([job_descriptions[:user_id],job_descriptions[:role_id]])
       .order(job_descriptions[:user_id].asc, 
              job_descriptions[:role_id].asc,
              job_descriptions[:role_version].desc)

    from(sub_query.as(table_name))
  }
end

SQL Generated: https://www.db-fiddle.com/f/7eoYV2gPQ2hfFJqpxJTxTr/1

SELECT 
  job_descriptions.*
FROM 
  (SELECT DISTINCT ON (job_descriptions.user_id,job_descriptions.role_id)
     job_descriptions.*
   FROM 
     job_descriptions
   ORDER BY 
     job_descriptions.user_id ASC,  job_descriptions.role_id ASC, job_descriptions.role_version DESC
  ) job_descriptions

Database Agnostic: using an aggregate sub-query and joining it to the table

class JobDescription < ApplicationRecord
  scope :max_role_versions, -> {
    job_descriptions = arel_table
    sub_query = job_descriptions 
       .project(
          job_descriptions[:user_id],
          job_descriptions[:role_id],
          job_descriptions[:role_version].maximum.as('max_version'))
       .group(job_descriptions[:user_id], job_descriptions[:role_id])
       .as('max_versions')
    joins(
      Arel::Nodes::InnerJoin.new(subquery, 
        subquery.create_on(
          job_descriptions[:user_id].eq(subquery[:user_id]).and(
            job_descriptions[:role_id].eq(subquery[:role_id])).and(
            job_descriptions[:role_version].eq(subquery[:max_version]))
        )
      )
    ) 
  }
end

SQL Generated: https://www.db-fiddle.com/f/3zL18pzCYKx1cyWk8fSSaB/0

SELECT 
  job_descriptions.*
FROM 
  job_descriptions
  INNER JOIN (
      SELECT
        job_descriptions.user_id,
        job_descriptions.role_id,
        MAX(job_descriptions.role_version) as max_version
      FROM 
        job_descriptions
      GROUP BY 
        job_descriptions.user_id,job_descriptions.role_id
    ) AS max_versions ON job_descriptions.user_id = max_versions.user_id
            AND job_descriptions.role_id = max_versions.role_id
            AND job_descriptions.role_version = max_versions.max_version