Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Performing Top-N Queries in Oracle-3 : Page 3




Application Security Testing: An Integral Part of DevOps

Using the ROWNUM Pseudocolumn
One way to solve this problem is by using the Oracle pseudocolumn ROWNUM. ROWNUM is available in Oracle versions 7, 8.0 and 8i (8.1); I am not sure about Oracle 6. 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. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use the ROWNUM pseudocolumn to limit the number of rows returned by a query to 5:

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

Listing 2 shows the output of the above code. As you see, the first five rows have been returned.

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause.

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

Listing 3 shows the output of the above code.

Watch Out! Because the ROWNUM is assigned upon retrieval, it is assigned prior to any sorting! This is opposite to the result you would get in SQL Server using the TOP clause. In order to select employees with the highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is the syntax for a top-N query where N = 5 (this syntax with the subquery works only in Oracle 8i):

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;

Listing 4 shows the output of the above code. I used the NVL() function to sort the expression because sorting just by Emp_Salary would have put all records with NULL salary before those with the highest salaries, and that's not what we wanted to achieve.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date