Using Top-N Queries In Oracle.

############ CONNECTING IN ORACLE DATABASE #######################

SQL> CONN RND/RND
Connected.

############ CREATING A TABLE FOR TESTING TOP N QUERY #############

SQL> CREATE TABLE TOP_N_ANALYSIS (ID NUMBER, NAME VARCHAR2(30), SALARY NUMBER);

Table created.

############  INSERTING DATA INTO TABLE #############################

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(10,'RAJIB', 5000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(20,'JOHN', 4000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(30,'KING', 9000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(40,'MORGAN', 2000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(50,'William', 11000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(60,'Steven', 8000);

1 row created.

SQL> COMMIT;

Commit complete.

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY ###############

SQL> SELECT ROWNUM AS RANK, ID, NAME, SALARY FROM(
  2  SELECT ID, NAME, SALARY
  3  FROM   TOP_N_ANALYSIS
  4  ORDER BY SALARY DESC)
  5  WHERE ROWNUM<=3;

      RANK         ID NAME                               SALARY
---------- ---------- ------------------------------ ----------
         1         50 William                             11000
         2         30 KING                                 9000
         3         60 Steven                               8000

########## SELECT LOW 3 PERSON WHO GET LOWEST SALARY ###############

SQL> SELECT ROWNUM AS RANK, ID, NAME, SALARY FROM(
  2  SELECT ID, NAME, SALARY
  3  FROM   TOP_N_ANALYSIS
  4  ORDER BY SALARY)
  5  WHERE ROWNUM<=3;

      RANK         ID NAME                               SALARY
---------- ---------- ------------------------------ ----------
         1         40 MORGAN                               2000
         2         20 JOHN                                 4000
         3         10 RAJIB                                5000

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY USING RANK() FUNCTION ########

SQL> SELECT  ID, NAME, SALARY
  2  FROM   (SELECT SALARY, ID, NAME,
  3                 RANK() OVER (ORDER BY SALARY DESC) AS RANK_VAL
  4          FROM   TOP_N_ANALYSIS)
  5  WHERE  RANK_VAL <= 3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        50 William                             11000
        30 KING                                 9000
        60 Steven                               8000

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY USING DENSE_RANK() FUNCTION ########

SQL> SELECT  ID, NAME, SALARY
  2  FROM   (SELECT SALARY, ID, NAME,
  3                 DENSE_RANK() OVER (ORDER BY SALARY) AS RANK_VAL
  4          FROM   TOP_N_ANALYSIS)
  5  WHERE  RANK_VAL <= 3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        40 MORGAN                               2000
        20 JOHN                                 4000
        10 RAJIB                                5000