GREATEST Function

[Karthik_MaRa]

According to the function description, the function should return the maximum value among the specified parameters.
It works sometimes and does not function certain times.

SELECT GREATEST ('111', '222', '333', '444') "Greatest" FROM DUMMY;

The output of the above is "444" and this is valid.

SELECT GREATEST ('99', '222', '333', '444') "Greatest" FROM DUMMY;

This one returns "99" as the Greatest Value, Why?

Even after changing the value position also, the output is "99" only.

[Abhishek_Hazra · Accepted solution]

Hi

You should remove the single quotes while comparing numbers, else it will consider the ascii/unicode value of chars in comparison. & '99' has the greatest value in that order.

You can check the actual values being compared here with the following sqls :

select ascii('99') as asc_99 from dummy ;
select ascii('222') as asc_222 from dummy ;
select ascii('333') as asc_333 from dummy ;
select ascii('444') as asc_444 from dummy ;

So, in order to get the correct numeric comparison you should try like below :

SELECT GREATEST (99, 222, 333, 444) "Greatest" FROM DUMMY;

or below :

SELECT GREATEST (to_int('99'), to_int('222'), to_int('333'), to_int('444')) "Greatest" FROM DUMMY;

Best Regards,
Abhi

[michael_eaton3]

In your example, the function is operating on a character datatype, and database systems mostly sort on the order of characters, so in your case a value starting with a '9' is the greatest. If you want the greatest numeric value, use a numeric datatype.