Inline Views & Top N Analysis

The Inline view: It is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

           

Top N Analysis: The task of retrieving the top or bottom N rows from a database table. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK () and DENSE_RANK ().

Using the ROWNUM Pseudocolumn
One-Way to solve this problem is by using the Oracle pseudocolumn ROWNUM. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

E.g. To select top 5 rows

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

   FROM

   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

      FROM Emp

      ORDER BY NVL (Sal, 0) DESC)

   WHERE ROWNUM < 6;

Utilizing Oracle 8i's Ranking Functions

Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions.

SELECT Empno, Ename, Job, Mgr, Sal,

   RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Rank,

   DENSE_RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Drank

   FROM Emp                             

   ORDER BY SAL Desc NULLS LAST;

The difference between RANK () and DENSE_RANK () is that RANK () leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK () but only fourth place using RANK (). The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.