I have duplicated document number, value and version I need to return only the lines with the document with the max version

Document|value|version
A20|100|1
A20|200|2
A24|100|1
A24|300|2
A24|200|3
A25|100|1
A26|100|1

expected result to return only the last document version

Document|value|version
A20|200|2
A24|200|3
A25|100|1
A26|100|1

Here is what I did but it return everything and not only the max version of the documents

SELECT MAX(FACT.VERSION), FACT.DOCUMENT, FACT.VALUE
FROM PUBLIC.FACT FACT
GROUP BY FACT.DOCUMENT, FACT.VALUE, FACT.VERSION

CodePudding user response：

SELECT FACT.* 
FROM PUBLIC.FACT FACT
INNER JOIN 
(SELECT FACT2.DOCUMENT, MAX(FACT2.VERSION) AS HVERSION
FROM PUBLIC.FACT FACT2
GROUP BY FACT2.DOCUMENT) HV
ON HV.DOCUMENT = FACT.DOCUMENT
AND HV.HVERSION = FACT.VERSION

CodePudding user response：

Try this:

SELECT FACT.Document, FACT.value, FACT.version
From (
        SELECT *, ROW_NUMBER() OVER(partition by Document order by FACT.value DESC) as rn
        FROM PUBLIC.FACT 
     ) FAC
WHER rn = 1

CodePudding user response：

This is a common question on SO and appears in business frequently. It's helpful to think of it as a pattern.

1. Identify the max version by grouping field(s) in a CTE or subquery or temp table
2. Use the recordset from 1. to join to the original table on the grouping fields and max_version = version

Like so:

SELECT X.*
FROM PUBLIC.FACT X
INNER JOIN (SELECT DOCUMENT, MAX(VERSION) AS MAX_VERSION
            FROM PUBLIC.FACT
            GROUP BY DOCUMENT) Y ON X.DOCUMENT = Y.DOCUMENT AND X.VERSION = Y.MAX_VERSION;

dbfiddle.uk