shadab shadab - 1 year ago 68
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 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 Source

Try this

select d.* from documents d
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download