CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
re5et
re5et

Reputation: 4275

with activerecord how can I select records based on the highest value of a field?

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?

Upvotes: 1

Views: 1681

Answers (2)

subhash
subhash

Reputation: 286

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/

Upvotes: 0

Rakesh
Rakesh

Reputation: 443

Revision.maximum(:revision,:conditions => ["saved=1"],:group => 'name')

Upvotes: 6

Related Questions