Getting the 10th row of a table

The emp table has 14 records.

The query

select * from emp where rownum = 10
returns no rows. WHY ??? And what is an alternate solution to this?

First, why are you using rownum to select a record? Do you truly want the 10th record returned from the result set or are you trying to get employee number 10? Remember that rownum is simply a pseudocolumn that is assigned in the order that Oracle selects the row from the table. This means that row 10 could be different every time (in theory).

But, to answer your question specifically I'll quote from the Oracle documentation (the example uses a WHERE ROWNUM > 1 condition but the same concept applies to your situation WHERE ROWNUM = 10): "Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows: SELECT * FROM emp WHERE ROWNUM > 1; The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned."

Bottom line, the only way to use ROWNUM effectively is to use it to check for conditions that are less than (<) or less than or equal to (<=).

If you really want to get the 10th row only, you can do it this way:

SELECT * FROM emp WHERE rownum <= 10  MINUS  SELECT * FROM emp WHERE rownum
< 10 ;

The first SELECT will return the first 10 rows and the MINUS operator will subtract the first 9 rows so that you end up with just the 10th row.

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • 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.

Dig Deeper on Oracle and SQL