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:
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?
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