Can one retrieve only the Nth row from a table?

SELECT * FROM t1 a
WHERE  n = (SELECT COUNT(rowid)
             FROM t1 b
            WHERE a.rowid >= b.rowid);
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.

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.

Related Documents

How does one escape special characters when writing SQL queries?

Can one select a random collection of rows from a table?

How does one eliminate duplicates rows from a table?

Can one retrieve only rows X to Y from a table?

How can one dump/ examine the exact content of a database column?

How does one add a column to the middle of a table?

How does one select EVERY Nth row from a table?

How does one select the LAST N rows from a table?

How does one select the TOP N rows from a table?

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Map/ concatenate several rows to a column

How does one code a matrix/crosstab/pivot report in SQL?