How do I get rows with max values across two columns

Question

I would like to know how to filter down a table by getting the max values from two columns. Here is a simplified input table. I want one row per person which has the max(year) and max(version)

Name	Year	Version	Value
a	2017	1	100
a	2018	4	101
a	2018	5	102
b	2017	1	201
b	2017	2	202
b	2017	3	203
b	2016	2	204

What I want to get back is the rows with the highest years and then the rows with the highest years and the highest versions.

Name	Year	Version	Value
a	2018	5	102
b	2017	3	203

I used the following code and got a table with the latest years. But I have multiple entries for each year with different version numbers. How do I weed it down further with just the latest version numbers.

SELECT A.NAME, A.YEAR, A.VERSION, A.VALUE
FROM TABLE A
INNER JOIN (
    SELECT NAME, MAX(YEAR) AS YEAR
    FROM TABLE
    GROUP BY NAME
) B ON A.NAME = B.NAME AND A.YEAR = B.YEAR

Thank you.

Answer 1

Performance aside, this generic SQL should do it:

SELECT *
FROM t
WHERE NOT EXISTS (
    -- select rows for that person-year-version
    -- where year is greater or (year is same and version is greater)
    SELECT 1
    FROM t AS x
    WHERE x.Name = t.Name AND (
        x.Year > t.Year OR (
            x.Year = t.Year AND x.Version > t.Version
        )
    )
)

If window (analytic) functions are available then just use this query:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Year DESC, Version DESC) AS rn
    FROM t
) AS x
WHERE rn = 1