Rails 2.3.8 : how can I select records based on the highest value of a field?

I am rephrasing a question asked few days back .click this to see the previous question

I have answered this question.But it is not correct.

Records in the table

Revision.find(:all,:select => "id,name,max(revision) as revision", :conditions => ["saved=1"],:group => 'name')

which would result

Actually the result should hav开发者_JS百科e been id 3,6,8.

what modification in query will lead this result?

This is a query type to which SQL isn't well-suited. The problem, in a nutshell, is that what you effectively want is a group operation, and then a sort within each group, and then to take the top record from each sub-group. This turns out to be surprisingly difficult problem, because SQL does grouping before sorting. Generally, grouping is for aggregate data, rather than selecting specific records.

There are a number of SQL-specific ways to solve this, but none of them fit Rails very well. Instead, I'd structure your data like so:

1. Have a recipe_revisions model, which includes a full recipe record
2. Have a recipes model, which is basically just id/name/latest revision.
3. To get the current copy of a recipe, you'll select the recipe you want from recipes, then join to recipe_revisions on name/max revision, where those columns are indexed on both tables.

This isn't an easy solution, it'll work well.

Split your query into two. In the first find the max revision for each name. In the second query find the full row which matches name and revision.

CREATE TEMP TABLE max_revisions (name varchar, revision integer);
INSERT INTO max_revisions SELECT name, max(revision) FROM revisions
       WHERE saved = 1 GROUP BY name;
SELECT r.* FROM revisions r INNER JOIN max_revisions m
       ON m.name = r.name AND m.revision = r.revision;

Now your problem may be, how to express this in Rails.

You may use Revision.connection.execute and then Revision.find_by_sql (both wrapped in a single method, for example: Revision.find_by_max_revisions).

Otherwise, if your database does not support temporary tables, or you just don't want them, you may read the max_revisions into memory, and then use it to build the query:

class Revision
  def self.find_by_max_revisions
    max_revs = connection.select_values(
      sanitize_sql(
        ["SELECT name, max(revision) as max_rev FROM #{table_name}
               WHERE saved = ? GROUP BY name", 1]
      ), "Load max_revisions for #{self.name}"
    )
    max_revs.map do |row|
      find_by_name_and_revision(row["name"], row["max_rev"].to_i)
    end
  end
end

Revision.find(:all, :select => "DISTINCT id", :conditions => ["saved=1"], :order => 'revision DESC')

I have a similar problem, and I haven't been able to translate this to active record syntax, but a coworker helped me whip up a nested select that solved this problem for me. Something like this:

select id, name, saved, revision
from revisions r
where saved = 1 and
version_no = (select revision 
              from revisions r2
              where saved = 1 and r2.name = r.name
              order by r2.revision desc
              limit 1 
             )

Caveat: I'm not sure of the performance implications though, as I suspect this is making a second sub-query for every unique revision field.