SQL Query to Retrieve Non-Duplicate-Row

Question

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).

Yes, `group by` and `max(version)` would be the way to go indeed. You need to group by all fields that aren't aggregated. — David Hedlund, Nov 07 '16 at 09:51
If you are using SQL Server, you can use ROW_NUMBER() analytic function to achieve this. — Prafulla, Nov 07 '16 at 10:04

score 0 · Accepted Answer · answered Nov 07 '16 at 09:56

0

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

answered Nov 07 '16 at 09:56

jai dutt

780
6
13

Thanks that did work. – shadab Nov 07 '16 at 10:33
wha you negetive this answer – jai dutt Nov 07 '16 at 10:43
I had accidentally clicked it twice. – shadab Nov 07 '16 at 11:05

score 0 · Answer 2 · answered Nov 07 '16 at 10:13

If you are using sql-server and not postgresql then a window function will help:

With orderedVersions As (
    Select id, user_id, unique_document_id, Version, [date], RowNo = Row_Number() Over (Partition By Unique_Document_id Order By Version desc)
      From documents)
Select id, user_id, unique_document_id, Version, [date]
  From orderedVersions
  Where RowNo = 1;

So, yes you can achieve it by modifying the query. If the table is very large, then you might also consider performance tuning by creating indexes, but this query should get you started.

score 0 · Answer 3 · answered Nov 07 '16 at 10:39

0

Try this, (Works for MySql)

select a.* from doc_table a 
where (a.user_id, a.Unique_document_id, a.version) in
(select d.user_id, d.Unique_document_id, max(d.version)
from doc_table d group by d.user_id, d.Unique_document_id)

answered Nov 07 '16 at 10:39

Sahil Shaikh

161
8

SQL Query to Retrieve Non-Duplicate-Row

3 Answers3