CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Rakesh
Rakesh

Reputation: 443

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

alt text

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

which would result

alt text

Actually the result should have been id 3,6,8.

what modification in query will lead this result?

Upvotes: 0

Views: 1776

Answers (4)

simonvandyk
simonvandyk

Reputation: 41

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.

Upvotes: 0

Steve Lorek
Steve Lorek

Reputation: 111

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

Upvotes: 0

Arsen7
Arsen7

Reputation: 12820

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

Upvotes: 0

Chris Heald
Chris Heald

Reputation: 62648

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.

Upvotes: 1

Related Questions