Re: To display 5 Largest values from table

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 21 Mar 2003 06:33:50 -0800
Message-ID: <2687bb95.0303210633.6d0de616@posting.google.com>

param_gcs_at_hotmail.com (SQL_SAVVY) wrote in message news:<8e3f64bc.0303210334.3f1812c_at_posting.google.com>...
> i want to display first 3 persons with largest salary

From my 8i Upgrade paper for the development staff

TOP-N Prior to 8I finding the maximum or minimum value in a column was fairly easy to find using pure SQL by applying the min and max functions, but finding the top N or bottom N rows was difficult using pure SQL. By now using 8I you can do the following:

UT1> l
  1 select empno, ename, sal
  2 from ( select b.empno, b.ename, b.sal

  3         from   emp b
  4         order by b.sal desc
  5       )

  6* where rownum < 4
UT1> /

     EMPNO ENAME SAL
---------- ---------- ----------

      7839 KING             5000
      7788 SCOTT            3000
      7902 FORD             3000

This type of query is known as a Top-N query. The column list selected in the outer query must match the column list of the inner query exactly.

HTH -- Mark D Powell -- Received on Fri Mar 21 2003 - 08:33:50 CST