Search code examples
db2

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.