Top-N Analysis in Oracle database

[LaMarcus]

I am developing a mini project of forms and reports in oracle and need to Write a procedure that retrieves all rows from a database table within a sorted form whatever form I would specify. Can you fulfill my expectations through your response and that would be a great help for me develop my project.

[Praetor]

Top-N Analysis :
The task of retrieving rows from top of the table or bottom using particular column is commonly called as a "Top-N Analysis" and this method is used to build the application (java application,form and reports).

The internal process of Using this method, you can retrieved all rows from the database and the selected data would be filtered using ROWNUM pseudocolumn or RANK function.

[Katty]

Using ROWNUM function

ROWNUM pseudocolumn generates a number as a signal which indicates to oracle database, selects the rows from a table according to set of numbers and return the result after combining each row in relational format.It would be achieved like this-

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6 ORDER BY Sal;


This query will return the 5 rows in Ascending order of their salaries.

[kelfro]

Using RANK():

Another function that is provided by oracle database through which you can perform top-N analysis. it uses subquery to complete this operation and it takes the following form :

SELECT Empid, Empname, Job, Mgr, Hire_date, Salary
FROM

(SELECT Empid, Empname, Job, Mgr, Hire_date, Salary,
RANK() OVER
(ORDER BY SALARY Desc NULLS LAST) AS Emp_Rank FROM Emp ORDER BY SALARY Desc NULLS LAST)
WHERE Emp_Rank < 6;