Problem solve
Get help with specific problems with your technologies, process and projects.
Oracle's equivalent of SQL Server's TOP command
I want to retrieve the first row in Oracle from a table, like when using the TOP keword in SQL Server (select top 1 from emp). Is there any similar command in Oracle?
If you just want the first row in the result set (non-ordered), you can simply use ROWNUM as follows:
SELECT * FROM emp WHERE rownum < 2 ;If you want the first row of an ordered result set, you need to do the following:
SELECT e.* FROM (SELECT * FROM emp ORDER BY empno) e WHERE rownum < 2 ;The reason why is because rownum is assigned to the result set as the records are retrieved but before they are sorted. For that reason you must order the result set in an in-line view before using rownum to retrieve the first row (or any number of first rows for that matter).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs 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, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Start the conversation
0 comments