Select top n record in Oracle PL/SQL | Ranking result in Oracle PL/SQL using ROWNUM

Oracle assigns a number with each row of the result set accessed using SELECT statement. This number is called ROWNUM pseudocolumn. ROWNUM can be used to return top few records from the table. We can rank result set of the SELECT query by including ROWNUM in the SELECT statement

SELECT ROWNUM AS "RANK",
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  SALARY
FROM EMPLOYEES
WHERE ROWNUM<=10;

2014-01-04 16_31_55-Oracle SQL Developer _ system-XE

Here, we are displaying top 10 rows by comparing ROWNUM in WHERE clause. We are ranking results just by including ROWNUM column in the SELECT statement.

ROWNUM works fine with simple SELECT statements but when we include ORDER BY clause, it applies ROWNUM first and then ORDER BY. For example if we include ORDER BY SALARY DESC in the above query to rank top 10 employees by their SALARY we get following result

SELECT ROWNUM AS "RANK",
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  SALARY
FROM EMPLOYEES
WHERE ROWNUM<=10
ORDER BY SALARY DESC;

2014-01-05 00_42_31-Oracle SQL Developer _ system-XE

Here, first result sets are ranked and then ORDER BY SALARY is applied. Thus RANK is not in correct sequence. To rank query result with ORDER BY clause correctly we need to use subquery as following

SELECT ROWNUM AS "RANK",
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  SALARY
FROM
  (SELECT EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    SALARY
  FROM EMPLOYEES
  ORDER BY SALARY DESC
  )
WHERE ROWNUM<=10;

2014-01-05 00_52_21-Oracle SQL Developer _ system-XE

Here, first we apply ORDER BY in a subquery and then we rank the result using ROWNUM to rank the result set in correct order.

Leave a Reply

Your email address will not be published. Required fields are marked *