Find MAX value on ALN field with special characters in DB2

Determine the max value of an attribute that has values like below,

GROUPNAME
 A-1000
 C-1001
 A-1002
Expected Output 
 1002

I tried the below query, and it is giving the output as 1001 instead of 1002, the max value is based on the alphabet rather than the number,

select max(groupname) from table where type in ('A','C') and customer is null
Output 
1001
 

>Solution :

We can use a limit query here:

SELECT *
FROM yourTable
WHERE type IN ('A', 'C') AND customer IS NULL
ORDER BY CAST(SUBSTR(groupname, INSTR(groupname, '-') + 1) AS INT) DESC
LIMIT 1;

The strategy above is to isolate the number which comes after the dash, cast it to an integer, then use that for sorting.