shadab shadab - 27 days ago 11
SQL Question

sql query to retrieve non duplicate row

I have a table with the name Documents

id user_id Unique_document_id version date
1 1 1001 1 null
1 1 1001 2 null
1 2 1002 1 null


Currently I have a query which selects and displays the details

select d.* from documents d
where d.user_id = 1
order by d.date desc
limit 10
offset 0


But I want non duplicate rows using unique_document_id and having max(version).

The ouput should be : (1,1,1001,2,null), (1,2,1002,1,null)


Can I achieve this by modifying the query or do I need to handle this at other place?
Something using group by unique_document_id and max(version)

Answer

Try this

select d.* from documents d
INNER JOIN (
SELECT Unique_document_id, MAX(version) version
from documents
GROUP BY Unique_document_id) b ON a.Unique_document_id= b.Unique_document_id  
AND a.version = b.version
Comments