Oracle: How can I retrieve a specified number of highest- or lowest-value columns with a single query?

Answer 1

Example For Get Maximum Value:

select max(column_name) from Table_Name;

Example For Get Minimum Value:

select min(column_name) from Table_Name;

Answer 2

oracle refers to this as an "TOP-N Query".
Think of this as a two-step process. In the first step, you create a view with an ORDER BY clause that orders records in ascending or descending values of a specified column. You then select the top required number of records from this view using ROWNUM.

You could combine both of these steps in a single query by selecting the specified number of records from an in-line view whose records are ordered in ascending or descending order of the required column.

Thus, to retrieve the three employees in the EMP table with the highest salaries, you could write the following query:

SQL> SELECT ename, sal
FROM (SELECT ename, sal
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <= 3;

To retrieve the two departments in the EMP/DEPT tables with lowest total salaries you could write the following query:

SELECT d.dname, e.total_sal
FROM (SELECT deptno, sum(sal) total_sal FROM emp
GROUP BY deptno
ORDER BY sum(sal) ASC) e, dept d WHERE d.deptno = e.deptno
AND ROWNUM <= 2;