How to get Max value from last two digits out of max 3 in Postgresql?

Question

I want to trim some numbers to two digits and get the max value from it.

My table column is called "thousands" (int4) and has values like:

• 912
• 905
• 900
• 11
• 7
• 6

I expect to receive the value 12, because after trimming the numbers to a max. of two digits, 912 should be transformed to 12 which is higher than 11, but I receive 7 using the query:

SELECT MAX(RIGHT("thousands"::varchar, 2)) 
FROM "numbers"

I guess RIGHT([string], [number of digits from right to left]) is messing up when the value has less digits than requested, but I don't know how to achieve the demanded solution. Can anybody help please?

Answer 1

demo:db<>fiddle

RIGHT() returns an text output. So, MAX() is applied to this output and uses alphabetical order. In this order, 7 comes after 12 because 7 > 1 (1 as the first character of 12). To avoid this, you could cast the RIGHT() result into type int afterwards before using MAX():

SELECT
    MAX(RIGHT(thousands::text, 2)::int)
FROM t

The better and faster ways would be using numeral operations instead of double casting your values. So, the last to digit of a number can be calculated using the modulo operation %:

SELECT
    MAX(thousands % 100)
FROM t