Systems Engineering and RDBMS

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
FROM dual;

Greatest_Value
———————————————————————
SQL Server

SELECT LEAST(‘ORACLE’,’SQL Server’,’DB2′,’MYSQL’, 1234)  as Least_Value
FROM dual

Least_Value
———————————————————————-
1234

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”
FROM dual

Greatest            Least
----------            -------
1/12/2009 8:54:39 PM    1/7/2009 8:54:39 PM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: