A sub-query with aggregate functions (or group functions) in Oracle - Oracle

I already introduced sub-queries in the previous section.  In this section, I shall start giving you some practical examples.

Let us consider that I would like to retrieve the details of the highest paid employee. Let us write the question in a meaningful manner and identify the steps as follows:

From the above figure, you have two steps to work with for the query.  The following is the order you must follow (based on the above figure):

• Find the highest salary from the table (1)
• Based on the value you get, retrieve the employee details like empno, ename, etc. belonging to that salary. (2)

The following is the statement which retrieves the highest salary:

SELECT MAX(sal) FROM emp

To retrieve an employee's details based on a given salary (say 5000), the query would be as follows:

SELECT empno,ename,sal,deptno FROM emp

WHERE sal = 5000

Just replace the value 5000 with the query that gives you the highest salary.  The complete statement would be as follows:

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE sal = (SELECT MAX(sal) FROM emp);

Now, let us walk through its execution:

• The innermost query gets executed first. 
• In this case, the query “select max(sal) from emp” gets executed first.  It retrieves the highest value in the column “sal” from the table “emp.”
• Once the innermost query gets executed, it returns a value to the immediate outer query.  In this case, it is 5000.
• The entire innermost query gets replaced with the new value returned by it.  In this case, the outer query virtually becomes “select empno, ename, sal, deptno from emp where sal = 5000.”
• And finally, the outer query gets executed, which retrieves KING’s details.

Let us end this section with a final touch.  Why can’t I write the above query as follows?

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE sal = MAX(sal)

Or even the following:

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE MAX(sal) = sal

None of the above two queries get executed successfully. The reason is that a condition in a WHERE clause cannot contain any aggregate function (or group function) without a sub-query!