Using Oracle functions in ORDER BY and GROUP BY clauses

This tip discusses using functions to change the order of records in Oracle development.

There are two common approaches to changing the order of records in Oracle development: dynamic SQL and decode functions.

I recommend using functions--they have both flexibility and easy-to-understand code. Here is an example:

CREATE OR REPLACE FUNCTION EMP_ORDER(p_type IN number, 
                                    p_salary IN number, 
                                    p_comm IN number) 
RETURN NUMBER 
IS 
BEGIN
   if p_type = 1 then          -- by salary
      RETURN p_salary;
   end if;
   if p_type = 2 then          -- by comm/salary
      RETURN nvl(p_comm,0)/p_salary;
   end if;
   EXCEPTION  WHEN OTHERS THEN
     dbms_output.put_line(sqlerrm);
     RETURN 0;
END EMP_ORDER;
/

prompt order employees dynamically
prompt  order by salary
SELECT ename,
       comm,
       sal
FROM   emp   
ORDER by EMP_order(1, sal, comm) desc;

prompt  order by comm/salary
SELECT   ename,
         comm,
         sal
FROM   emp   
ORDER by EMP_order(2, sal, comm) desc;

In addition to using functions in ORDER BY clauses, it is sometimes beneficial to use them in GROUP BY clauses. For example:

CREATE OR REPLACE FUNCTION EMP_TIER(p_salary IN number) RETURN varchar2 IS 
BEGIN
   if    nvl(p_salary,0) < 1000  then -- junior
      RETURN 'junior';
   elsif nvl(p_salary,0) < 2000  then -- intermediate
      RETURN 'intermediate';
   else                               -- senior	              
      RETURN 'senior';
   end if;
   EXCEPTION  WHEN OTHERS THEN
     RETURN 'error';
END EMP_TIER;
/ 

prompt cluster employees by salary
column tier format a15
select  EMP_TIER(sal) tier, 
        count(1) Count_Employees
from emp 
group by EMP_TIER(sal);

For More Information


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close