top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

+2 votes
754 views
Oracle: How can I retrieve a specified number of highest- or lowest-value columns with a single query?
posted Feb 10, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+2 votes

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 Feb 10, 2015 by Balamurugan Kn
+1 vote

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;

answer Feb 11, 2015 by Arun Gowda
...