Search code examples
mysqlsqlgreatest-n-per-groupzend-db-table

Get lastest version of a content group SQL

I have content table where I have many contents and various versions of those contents. The columns are content varchar,document_id int (identifies a content between many versions) and version int

I want to return the lastest version of each content group. I tried using this SQL but it returns me just the first version but I want the last.The order by doesn't work with group by.

SELECT * FROM content where id_project = 8 group by document_id order by version desc;

How can i get the lastest version of all unique content(unique content is identified by the document_id)?

Thanks if anyone can help me

Solution 
  • SELECT c1.* FROM content c1
  left join content c2 on c1.document_id = c2.document_id and c1.version < c2.version
 where c2.document_id is null
   and c1.id_project = 8