sql - How do I write a Rails ActiveRecord scope that combines rows and returns only latest date amongst three dates? -

i have rails application table this:

 id | parent_id | datetime_a          | datetime_b          | datetime_c 1  | 55        | 2013-08-03 11:00:00 | null                | null 2  | 55        | null                | 2013-08-04 13:01:11 | null 3  | 56        | 2013-08-02 17:33:23 | null                | null 4  | 56        | null                | 2013-08-01 18:00:00 | null 5  | 56        | null                | null                | 2013-07-12 07:45:00 

i want write 3 activerecord scopes return single record per parent_id , chooses record latest datetime particular datetime_x column , narrows down rows datetime_x column filled. if particular parent_id has records more 1 datetime_x column, should return row if datetime_x narrowed latest of existing. scope datetime_a return:

 id | parent_id | datetime_a          | datetime_b          | datetime_c 3  | 56        | 2013-08-02 17:33:23 | null                | null 

we row parent_id 56 because datetime_a latest of 3 date times. no row returned parent_id 55 because datetime_b record 2 later datetime_b record 1.

 id | parent_id | datetime_a          | datetime_b          | datetime_c 2  | 55        | null                | 2013-08-04 13:01:11 | null 

we row parent_id 55 because record 2 has latest datetime of 3 datetime_x columns. no parent_id 56 records because datetime_a later datetime_b 3 parent_id 56 records.

this have far, not work: (assuming model foo, table foo):

scope :datetime_a, ->{ select('foo.parent_id, max(coalesce(datetime_a, datetime_b, datetime_c))').group('foo.parent_id').where('datetime_a not null') }

also tried this:

scope :datetime_a, ->{ select('distinct on (foo.parent_id) *').where('datetime_a not null').order('max(coalesce(datetime_a, datetime_b, datetime_c))')

i'm using postgres postgres-only answers welcome.

solved way:

scope :datetime_a, ->{ find_by_sql('select * (select distinct on (foo.parent_id) * foo order foo.parent_id, greatest(coalesce(datetime_a, datetime_b, datetime_c)) desc) uniq_parent uniq_parent.datetime_a not null')  }