Oracle SQL - Select Row Limiting

Introduction

You can retrieve Top-N row queries with the row limiting clause.

select empno, ename||','||init name, job, msal
from emp
order by msal desc, name;

select empno, ename||','||init name, job, msal
from emp
order by msal desc, name
FETCH FIRST 5 ROWS ONLY;

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w  w w  .  j av a  2s .  c o  m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> select empno, ename||','||init name, job, msal
  2  from emp
  3  order by msal desc, name;

     EMPNO NAME           JOB            MSAL
---------- -------------- -------- ----------
      7012 JONES,R        ADMIN          8000
      7009 KING,CC        DIRECTOR       5000
      7902 FORD,MG        TRAINER        3000
      7008 SCOTT,DEF      TRAINER        3000
      7004 JACK,JM        MANAGER        2975
      7006 BLAKE,R        MANAGER        2850
      7007 CLARK,AB       MANAGER        2450
      7001 SMITH,N        TRAINER        1800
      7002 ALLEN,JAM      SALESREP       1600
      7010 BREAD,JJ       SALESREP       1500
      7934 MARY,ABC       ADMIN          1300

     EMPNO NAME           JOB            MSAL
---------- -------------- -------- ----------
      7005 BROWN,P        SALESREP       1250
      7003 WARD,TF        SALESREP       1250
      7011 ADAMS,AA       TRAINER        1100

14 rows selected.

SQL>
SQL> select empno, ename||','||init name, job, msal
  2  from emp
  3  order by msal desc, name
  4  FETCH FIRST 5 ROWS ONLY;
FETCH FIRST 5 ROWS ONLY
*
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL>

Related Topics