Sql Tricks : Min(), Max(), ename for min sal, ename for Max sal in the same row

Let's say our requirement is to find out the Min() and Max() salaries of employees in each department with their names. So, the output we want is :

DEPTNO MAX_SAL MAX_ENAME MIN_SAL MIN_ENAME
10 5000 KING 1300 MILLER
20 3000 SCOTT 800 SMITH
30 2850 BLAKE 950 JAMES

Let's if a union does it for us ?

select deptno, ename, sal , 0 min_sal
from emp
where sal in (select max(sal) max_sal from emp group by deptno)
union
select deptno, ename, 0 , sal
from emp
where sal in (select min(sal) min_sal from emp group by deptno);

DEPTNO ENAME SAL MIN_SAL
10 KING 5000 0
10 MILLER 0 1300
20 FORD 3000 0
20 SCOTT 3000 0
20 SMITH 0 800
30 BLAKE 2850 0
30 JAMES 0 950

This gives the results, but not as we want...... both, min() and max() in the same row, with ename.

This can be achieved in 3 ways:

Solution 1 : This is the most simplest approach. Using max, min, substr(), to_number(), to_char()

select deptno, to_number (substr (xmax, 1, 14)) max_sal,
substr (xmax, 15) max_name, to_number (substr (xmin, 1, 14)) min_sal,
substr (xmin, 15) min_name
from (select deptno, max (to_char (sal, '0000000000.00') || ename) xmax,
min (to_char (sal, '0000000000.00') || ename) xmin
from emp
group by deptno);

Solution 2 : Traditional approach : Using Inline views

How about doing this : Let's calculate the max(sal), min(sal) and get the enames of employees having those sal's.

select deptno,
max_sal,
(select ename
from emp
where sal = max_sal) max_ename,
min_sal,
(select ename
from emp
where sal = min_sal) min_ename
from (select deptno, min(sal) min_sal, max(sal) max_sal
from emp
group by deptno
order by deptno);

You will get the error : "ORA-01472: single-row subquery returns more than one row."

If you notice, in deptno, there are two employees with the same max(sal) = 3000 [Ford and Scott].

So, when the query "select ename from emp where sal = max_sal' is getting executed, there are two rows
with max_sal value. If you try the above query only for departments 10,30, you won't get this error.

Let's take a look :

select deptno,
max_sal,
(select ename from emp where sal = max_sal) max_ename,
min_sal,
(select ename from emp where sal = min_sal) min_ename
from (select deptno, min(sal) min_sal, max(sal) max_sal
from emp
where deptno in (10, 30)
group by deptno
order by deptno);

So, how to get over the error above ? Include "rownum =1" in the where clause.
Here we go :

select deptno,
max_sal,
(select ename from emp where sal = max_sal and rownum =1) max_ename,
min_sal,
(select ename from emp where sal = min_sal and rownum =1) min_ename
from (select deptno, min(sal) min_sal, max(sal) max_sal
from emp
group by deptno
order by deptno);

DEPTNO MAX_SAL MAX_ENAME MIN_SAL MIN_ENAME
10 5000 KING 1300 MILLER
20 3000 SCOTT 800 SMITH
30 2850 BLAKE 950 JAMES


Solution 3 : Analytics always rock !!

select deptno, max(case when rn <> 1 then sal else null end) max_sal,
max(case when rn <> 1 then ename else null end) max_ename,
max(decode(rn, 1, sal)) min_sal, max(decode(rn, 1, ename)) min_ename
from (select *
from (select deptno,
row_number() over(partition by deptno order by sal) rn,
max(sal) over(partition by deptno) max_sal,ename, sal
from emp)
where rn = 1 or sal = max_sal)
group by deptno;

Source : AskTom.

Long Live "Tom Kyte".

Good Luck,
r-a-v-i