DISTINCT, SUBSTR & MAX in Oracle Database

I have a table like below on which I want to return the distinct server name with max value of last 4 digits from all permutations in that table:

SO basically I can get the unique combination using following query:

select DISTINCT SUBSTR(ServerName,1,6) from MYTABLE;

This gives me:

Now I want the query to return max values of last 4 digits which are basically incremental in HEX

Any suggestions would help! Thanks in advance

EDIT 1
Expected Result would be like:

So if table has these values:

Then query should return max value for each combination. For e.g:

>Solution :

We can use the ‘XX’ format mask with to_number() to convert hex into decimal. The number of X must match the number of characters in the hex string. Then it’s a simple aggregation.

with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG60DF' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'XCCPTV401D' as servername from dual 
)
select SUBSTR(ServerName,1,6) as server_name
      ,max(to_number(substr(servername, 7, 4), 'xxxx')) as server_no
from MYTABLE
group by SUBSTR(ServerName,1,6)

Demo on db<>fiddle