title: Can Rails' Active Record handle SQL aggregate queries?
body: <p>Just started learning active record and am wondering how to best retrieve data from multiple tables where an SQL aggregate query is involved.</p>
  
  <p>In the following example (from a medical app) I'm looking for the most recent events of various types for each patient (e.g. last visit, last labtest etc). As you can see from the sql query below I'm looking for the max(date) value from a grouped query. I resorted to find_by_sql to do this - however I'd like to see how to do this without using find_by_sql.</p>
  
  <p>IOW - how would you get the required data here using a pure ActiveRecord approach. Below are the Table and Class defs I'm testing with:</p>
  
  <p>Find by Sql to retrieve most recent entries for each type - note the 'max(event_date)' here</p>
  
  <pre><code>strsql = "select  p.lname, e.patient_id, e.event_type, max(e.event_date) as event_date 
       from events e   
           inner join patients p on e.patient_id = p.id
           group by p.lname, e.patient_id, e.event_type"
  </code></pre>
  
  <p>Here's the sample sql query result:</p>
  
  <pre>
  lname, patient_id, event_type, latest
  'Hunt', 3, 'Labtest', '2003-05-01 00:00:00'
  'Hunt', 3, 'Visit', '2003-03-01 00:00:00'
  'Seifer', 2, 'Labtest', '2002-05-01 00:00:00'
  'Seifer', 2, 'Visit', '2002-03-01 00:00:00'
  
  Table Relationships are:
  Tables ---> Patients --> Events
                                 --> visits
                                 --> labtests
                                 --> ... other
  patients
        t.string :lname
        t.date :dob
  
  events
        t.column :patient_id, :integer
        t.column :event_date, :datetime
        t.column :event_type, :string
  
  visits 
        t.column :event_id, :integer
        t.column :visittype, :string
  
  labtests
        t.column :event_id, :integer
        t.column :testtype, :string
        t.column :testvalue, :string
  </pre>
  
  <p>Classes</p>
  
  <pre><code>class Patient &lt; ActiveRecord::Base
    has_many :events
    has_many :visits, :through =&gt;:events
    has_many :labtests, :through =&gt; :events
  end
  
  class Event &lt; ActiveRecord::Base
    has_many :visits
    has_many :labtests
    belongs_to :patient
  end
  
  class Visit &lt; ActiveRecord::Base
    belongs_to :event
  end
  
  class Labtest &lt; ActiveRecord::Base
      belongs_to :event
  end
  </code></pre>