Donald K. Burleson Oracle Tips

Using Oracle greatest and least built-in functions in SQL

Oracle provides a wealth of extensions to the SQL language to enhance the productivity of SQL queries.  These extensions to standard SQL can be very useful for creating tabular reports directly from Oracle tables without the need for sophisticated query tools.

The greatest and least built-in functions are used to detect the highest and lowest values for a column.  The power of these functions become apparent when combined with the decode built-in function.  By using decode to change the ranges to a binary number, we can then use the sum built-in function to count the number of values within a specified range.  The query below shows how this works:

prompt Salary ranges by Department

select

   deptno,

   sum(decode(greatest(SAL,3001), least(SAL,9999), 1, 0))  "$3001-$9999",

   sum(decode(greatest(SAL,2001), least(SAL,3000), 1, 0))  "$2001-$3000",

   sum(decode(greatest(SAL,1001), least(SAL, 2000), 1, 0)) "$1001-$2000",

   sum(decode(greatest(SAL, 0), least(SAL, 1000), 1, 0))   "<  $1000"

from

   emp

group by

   deptno;

Here is the output:

Salary ranges by Department

    DEPTNO $3001-$9999 $2001-$3000 $1001-$2000   <  $1000

---------- ----------- ----------- ----------- ----------

        10           1           1           1          0

        20           0           3           1          1

        30           0           1           4          1

Internally, Oracle services this query by performing a full-table scan against the table, followed by a sort to get the desired counts.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm