Q: Convert long SQL query into ActiveRecord expression I need to convert the SQL query below to an active record query expression. I have come across a number of related posts that involve joining two different models using .joins()but in this case I am joining the model with a subset of itself. In particular, I could not find how to express the SELECT a.some_column, ... FROM my_model a
SELECT a.city_id, a.statistic_id, a.value, a.year
FROM city_values a
INNER JOIN (
  SELECT city_id, statistic_id, MAX(year) AS year
  FROM city_values
  GROUP BY city_id, statistic_id) b 
  ON a.city_id = b.city_id AND a.year = b.year and a.statistic_id = 
  b.statistic_id
  ORDER BY city_id, statistic_id;

I have a model named CityValue corresponding to data for various cities, statistics and years. The model has attributes city_id, statistic_id, value, year. The ActiveRecord query is intended to get the recent years statistics values for each city and statistic and return an ActiveRecord::Relation object.

A: From my understanding about the SQL query provided, you are trying to select the rows that contain the recent year statistics for each unique pair of (city_id, statistic_id). 
I would not use a join for this problem. cvs stands for city_values.
CityValue.
    select('DISTINCT ON (cvs.city_id, cvs.statistic_id), cvs.*').
    order('cvs.city_id, cvs.statistic_id, cvs.year DESC') 

Explanations about DISTINCT ON can be found here:
https://www.vertabelo.com/blog/technical-articles/postgresql-select-distinct-on.
