Clash Royale CLAN TAG#URR8PPP

Oracle SQL Developer: Dynamic Selection of Rows Where Value in Column = MAX Value

Please see example below to better understand my question:

Columns in table "Names":

Table and Columns

Goal is to get the maximum Version per ID. Output should be:

Output

What are the possible ways to achieve that? E.g. using self join on the table?

Thanks in advance.

1 Answer
1

If the name for an ID is always the same then this is simple aggregation; with sample data as a CTE:

-- CTE for sample data - same names
with your_table (id, name, version) as (
select 1, 'Name1', 1 from dual
union all select 1, 'Name1', 2 from dual
union all select 1, 'Name1', 3 from dual
union all select 2, 'Name2', 1 from dual
union all select 2, 'Name2', 2 from dual
)
-- actual query
select id,
name,
max(version) as version
from your_table
group by id, name
order by id;

ID NAME VERSION
---------- ----- ----------
1 Name1 3
2 Name2 2


If the name might be different and you want to see the name that corresponds to the maximum value for each ID then you can use the last function:

last

-- CTE for sample data - different names
with your_table (id, name, version) as (
select 1, 'Name1', 1 from dual
union all select 1, 'Name2', 2 from dual
union all select 1, 'Name3', 3 from dual
union all select 2, 'Name1', 1 from dual
union all select 2, 'Name2', 2 from dual
)
-- actual query
select id,
max(name) keep (dense_rank last order by version) as name,
max(version) as version
from your_table
group by id
order by id;

ID NAME VERSION
---------- ----- ----------
1 Name3 3
2 Name2 2


You can also use a subquery to assign a ranking to each row, which might be more convenient if you have lots of columns to retrieve for the maximum version:

select id, name, version --, other columns
from (
select id, name, version, -- other columns,
rank() over (partition by id order by version desc) as rnk
from your_table
)
where rnk = 1
order by id;

ID NAME VERSION
---------- ----- ----------
1 Name3 3
2 Name2 2


The rank() analytic functions adds a rnk column to the inline view's result set; the partition by and order by mean that the highest version for each ID is ranked 1, and the outer query then filters to only show those rows that are ranked 1.

rank()

rnk

partition by

order by

This assumes the combination of ID and version is unique, which seems reasonable. If it isn't then you need to decide how to break ties.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.