Active Record Equivalent to SQL Aggregate Query?

Hi, Just started learning active record and am wondering how to best retrieve data from multiple tables where an SQL aggregate query is involved.

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 learn how to do this type of query without using find_by_sql.

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:

# Find by Sql to retrieve most recent entries for each type - note the 'max(event_date)' here 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"

Here's the sample sql query result:

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

Classes

class Patient < ActiveRecord::Base   has_many :events   has_many :visits, :through =>:events   has_many :labtests, :through => :events end

class Event < ActiveRecord::Base   has_many :visits   has_many :labtests   belongs_to :patient end

class Visit < ActiveRecord::Base   belongs_to :event end

class Labtest < ActiveRecord::Base     belongs_to :event end

OP here - for completeness I'm adding a link to the solution provided on Stack Overflow:

BC