Selecting a column where another column is maximal

Question

I guess this is a standard problem. But I could not find a proper solution yet.

I have three columns in table A:

ID     ID_Version     Var
1        1            A
1        2            A
1        3            X
1        4            D
2        1            B
2        2            Z
2        3            D
3        1            A
4        1            B
4        2            Q
4        3            Z

For every unique ID, I would like to isolate the Var-value that belongs to the maximal ID-Version.

For ID = 1 this would be D, for ID = 2 this would be D, for ID = 3 this would be A and for ID = 4 this would be Z.

I tried to use a group by statement but I cannot select Var-values when using the max-function on ID-Version and grouping by ID.

Does anyone have a clue how to write fast, effective code for this simple problem?

Answer 1

use row_number() analytic function :

select ID,Var from
(
select row_number() over (partition by id order by id_version desc) as rn,
       t.*
  from tab t
)
where rn = 1

or max(var) keep (dense_rank...)

select id, max(var) keep (dense_rank first order by id_version desc) as var
  from tab 
 group by id

Demo

Answer 2

You could use ranking function:

SELECT *
FROM (SELECT tab.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID_Version DESC) rn
      FROM tab)
WHERE rn = 1

Answer 3

Oracle has the keep syntax, so you can also use aggregation:

select id, max(id_version) as id_version,
       max(var) keep (dense_rank first order by id_version desc) as var
from a
group by id;

Answer 4

You could also use a simple join to do what you want, see below :

SELECT A.id, A.var FROM A
JOIN
(SELECT id, MAX(id_version) as id_version
FROM A
GROUP BY id) temp ON (temp.id = A.id AND temp.id_version = A.id_version)

Or you could also use a subquery like this :

SELECT a1.id, a1.var FROM A a1
WHERE a1.id_version = (SELECT MAX(id_version) FROM A a2 WHERE a2.id = a1.id)