ActiveRecord nested SELECT -- can I do it without manual SQL?

Question

I have a table with (among other things) a name and a rank. I'd like to return the set of all unique names, but for each name returned, I'd like to pick the row with the highest rank. This is simple with two nested SELECT statements:

SELECT * FROM (SELECT * FROM foo ORDER BY rank DESC) AS ordered GROUP BY name

MySQL takes the first "hit" for each name, which (because of the earlier ORDER BY) will always be the highest-ranking one.

Now, if I want to wire into this table with ActiveRecord, I'm at a bit of a loss. I could just throw the above into find_by_sql, but that just feels dirty. I tried something like

result = foo.all
result.delete_if do |item|
  isOutranked = false
  result.each do |row|
    if (row.name == item.name) and (row.rank > item.rank) then isOutranked = true
  end
  isOutranked
end

I think that works, but it still feels like there ought to be a better way. Solving it either via ActiveRecord trickery or a more elegant array manipulation would be welcome!

Answer 1

MySQL takes the first "hit" for each name, which (because of the earlier ORDER BY) will always be the highest-ranking one.

The query you're using to return the top row in the group is not guaranteed. It's only a coincidence of the implementation, and it's subject to change. Do not rely on this.

You are trying to solve the "greatest-n-per-group" problem that I see posted on StackOverflow frequently. Here is a query that gets the answer more reliably:

SELECT t1.*
FROM foo AS t1
LEFT OUTER JOIN foo AS t2
 ON (t1.name = t2.name AND t1.rank < t2.rank)
WHERE t2.name IS NULL;

Alternative that does the same thing:

SELECT *
FROM foo AS t1
WHERE NOT EXISTS 
    (SELECT * FROM foo AS t2
     WHERE t1.name = t2.name AND t1.rank < t2.rank);

I could just throw the above into find_by_sql, but that just feels dirty.

ActiveRecord is very handy for certain kinds of queries, but you can't solve every database query with ActiveRecord. The sooner you get over this notion the sooner you will get your work done and be successful. SQL is not going to bite you.