1

I have run into something I don't know how to do with active record (actually I couldn't say exactly how to do it with sql) that I would like to do. I would like to select records based on the highest value of a certain field as well as some other criteria. Below is some pseudo stuff which explains my situation. Given the following records:

id:1 | name:recipe1 | saved:true  | revision:1
id:2 | name:recipe1 | saved:false | revision:2
id:3 | name:recipe1 | saved:true  | revision:3
id:4 | name:recipe1 | saved:false | revision:4
id:5 | name:recipe2 | saved:true  | revision:1
id:6 | name:recipe2 | saved:true  | revision:2
id:7 | name:recipe3 | saved:false | revision:1
id:8 | name:recipe4 | saved:true  | revision:1

I would like to be able to get the records with the highest revision number that has been saved. That would mean records with ids: 3, 6, and 8

My first instinct is to do some kind of subquery that gets a MAX on revision or something. Other than that, I don't really have any idea how to go about that so any help will be greatly appreciated.

EDIT:

this is the sql that does what I want:

SELECT id, name, MAX(revision) as "revision" FROM revisions WHERE saved = 1 GROUP BY name

Now, is there any reasonable way to do this with ActiveRecord?

shivam
  • 16,048
  • 3
  • 56
  • 71
re5et
  • 4,305
  • 3
  • 25
  • 26

2 Answers2

6
Revision.maximum(:revision,:conditions => ["saved=1"],:group => 'name')
Rakesh
  • 423
  • 1
  • 3
  • 17
  • this is returning the id and highest revision (like: {33=>4}) which is almost there, but is there any way to get it to return records? – re5et Nov 19 '10 at 07:41
  • Revision.find(:all,:select => "id,name,max(revision) as revision", :conditions => ["saved=1"],:group => 'name') – Rakesh Nov 19 '10 at 07:48
0

Use the CTE's ROW_NUMBER() function OVER (PARTITIONED BY recipe1 ORDER BY revision).

For more details please visit this page:

http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

subhash
  • 276
  • 1
  • 1