postgresql - How to select id with max date group by category in Ecto query with Phoenix? -

for example, select id max date group category, result is: 7, 2, 6

id  category  date 1           2013-01-01 2   b         2013-01-03 3   c         2013-01-02 4           2013-01-02 5   b         2013-01-02 6   c         2013-01-03 7           2013-01-03 8   b         2013-01-01 9   c         2013-01-01 

this sql think can work:

select * table1 t1 join  (    select category, max(date) maxdate    table1    group category ) t2 on t1.category = t2.category , t1.date = t2.maxdate 

but how translate query on ecto?

an issue many frameworks cannot capture complexities of sql select statement. easiest solution: wrap complex query in view:

create view my_complex_view   select * table1 t1   join (     select category, max(date) maxdate     table1     group category) t2   on t1.category = t2.category , t1.date = t2.maxdate; 

now have simple query (select * my_complex_view) decent framework can handle.