There are several ways to do this in Oracle. Perhaps the most simple is to make use of Oracle's pseudocolumn, ROWNUM--most other databases have a similar feature. For each row returned by a query, ROWNUM returns a number indicating the order in which Oracle retrieves the row. The first row selected has a ROWNUM of 1, the second has 2, and well, you get the picture.
I'm not familiar with the "emp" table Personal Oracle provides, but if Emp were a table and Salary were a column in that table, you might write the query this way:
SELECT * FROM (SELECT * FROM Emp ORDER BY Salary DESC) WHERE ROWNUM <= 5
Note the subquery. You need to tell Oracle to sort the rows before you restrict them by ROWNUM. ROWNUM is a handy feature that I haven't had to use much, but when I have needed it, it's been nice to have around.
However, there is a better way to do it. The above example is less than perfect SQL for three reasons. First, ROWNUM is proprietary Oracle. Second, ORDER BY used in this way is not standard SQL. Third and most important, ties are decided arbitrarily and you don't have any control over them. What if the fifth and sixth highest paid employees had the same salary? Which one would you show in your top five? The query below fixes these problems and will show the top five paid employees and if there is a tie on the fifth salary, it will show all of the employees with that salary. This seems to be the most logical approach. I should thank Joe Celko for this idea. I originally saw this done in the section on extrema functions in his book, SQL for Smarties. Take a look:
SELECT FirstName, LastName, Salary FROM Emp E1 WHERE ( SELECT COUNT(*) FROM Emp E2 WHERE E2.Salary > E1. Salary ) < 5
For every row, the subquery counts the number of rows that have a higher salary. If this count is less than the number of rows you wish to return, in this case 5, then the row is returned.
For More Information
- What do you think about this answer? E-mail the Edtior at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle and SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
For news, advice and other information about oracle basics and FAQs, click here.
This was first published in November 2001