Re: showing top x values in sql*plus

If you are able to use analytics, then try rank() or dense_rank()

Much simpler.

Here's an example using the beloved scott.emp table :-

• data with no predicates

SQL> select empno, ename, sal from scott.emp;

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

      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

• data ordered by rank of SAL

select empno, ename, sal, rank() over (order by sal desc) rr from scott.emp;

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

      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          4
      7698 BLAKE            2850          5
      7782 CLARK            2450          6
      7499 ALLEN            1600          7
      7844 TURNER           1500          8
      7934 MILLER           1300          9
      7521 WARD             1250         10
      7654 MARTIN           1250         10
      7876 ADAMS            1100         12
      7900 JAMES             950         13
      7369 SMITH             800         14

14 rows selected.

• data ordered by dense_rank of SAL

select empno, ename, sal, dense_rank() over (order by sal desc) rr from scott.emp;

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

      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          3
      7698 BLAKE            2850          4
      7782 CLARK            2450          5
      7499 ALLEN            1600          6
      7844 TURNER           1500          7
      7934 MILLER           1300          8
      7521 WARD             1250          9
      7654 MARTIN           1250          9
      7876 ADAMS            1100         10
      7900 JAMES             950         11
      7369 SMITH             800         12

14 rows selected.

• top 3 using rank()

select * from
(
select empno, ename, sal, rank() over (order by sal desc) rr from scott.emp
)
where rr <= 3;

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

      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2

• top 3 using dense_rank

select * from
(
select empno, ename, sal, dense_rank() over (order by sal desc) rr from scott.emp
)
where rr <=3
;

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

      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          2
      7566 JONES            2975          3

hope this helps
good luck Received on Tue May 27 2003 - 15:08:06 CEST