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:

CodePudding user response：

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)

Alternate solution: provided your NLS_SORT parameter is set to binary (i.e. sorting by ASCII value) alphanumeric sorting rules will sort hexadecimal numbers in increasing value. So you can just do this:

with mytable as (
select 'APPQTV1234' as servername from dual union all
select 'APPQTVA234' as servername from dual union all
select 'APPQTV1C34' as servername from dual union all
select 'IISLUG6578' as servername from dual union all
select 'IISLUG80DF' as servername from dual union all
select 'APPQTV4321' as servername from dual union all
select 'IISLUG9999' as servername from dual union all
select 'XCCPTV8049' as servername from dual union all
select 'XCCPTV401D' as servername from dual 
)
select SUBSTR(ServerName,1,6)                           
       || max(substr(servername, 7, 4)) as max_server_name
from MYTABLE
group by SUBSTR(ServerName,1,6)

Demo on db<>fiddle