Back to the Basics: Greatest() and Least() functions
Posted by decipherinfosys on January 9, 2009
GREATEST() and LEAST() are two built-in SQL functions in Oracle, that can be used to derive the largest and smallest values from the given expressions.
GREATEST returns the greatest value from list of expressions while LEAST returns least from the list of expressions. Datatype of the first expression is taken into consideration and data types of the rest of the expressions are converted to the data type of first expression implicitly before comparison. If there is a conflict in data type then error will be returned. Comparison follows the rule of data type comparison.
Following are couple of examples:
SELECT GREATEST(‘Oracle’,’SQL Server’,’DB2′,’MYSQL’) as Greatest_Value
SELECT LEAST(‘ORACLE’,’SQL Server’,’DB2′,’MYSQL’, 1234) as Least_Value
In the above example, even though 1234 is a numeric type, it got converted to varchar2 before comparison occurred as first expression is of type varchar2. If 1234 is the first expression then upon execution SQL will return an error. Similarly we can get the values for date as well.
SELECT GREATEST(SYSDATE, SYSDATE-1, SYSDATE+4) “Greatest”,
LEAST(SYSDATE, SYSDATE-1, SYSDATE+4) “Least”
Greatest Least ---------- ------- 1/12/2009 8:54:39 PM 1/7/2009 8:54:39 PM