shadab shadab - 29 days ago 26
Java Question

SQL Query to Retrieve Non-Duplicate-Row

I have a table

documents
(
id
,
user_id
,
unique_document_id
,
version
,
date
).

Example of entries are:

(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 output should be like:
(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