Can one retrieve only the Nth row from a table?

provided this solution to select the Nth row from a table:

SELECT * FROM t1 a
WHERE  n = (SELECT COUNT(rowid)
            FROM t1 b
           WHERE a.rowid >= b.rowid);

provided this solution:

SELECT * FROM (
 SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE  RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

provided these solutions:

SELECT f1 FROM t1
WHERE  rowid = (
      SELECT rowid FROM t1
      WHERE  rownum <= 10
      MINUS
      SELECT rowid FROM t1
      WHERE  rownum < 10);

SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...

SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN (SELECT rowid FROM emp WHERE rownum <>Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

Posted by Blog