Skip navigation.

Can one retrieve only the Nth row from a table?

Rupak Mohan 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);

Shaik Khaleel 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.

Ravi Pachalla 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 < 10);

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.

On Oracle 9.2 and above this

On Oracle 9.2 and above this will use the Oracle STOPKEY facility, which means that if sorting a million rows to find the 10th row, it will only ever keep the top 10 rows in memory while doing the sort - i.e. it's a very efficient way to get the first n rows.

Select * from                              
(SELECT *, rownum OUR_ROWNUM                   
 FROM   
  (SELECT emp.*                             
    FROM emp          
    WHERE . . . .   
    ORDER BY order criteria)              
    WHERE  ROWNUM <= 7                               
 )                                                     
 where our_rownum = 7

You can easily extend this to get the set of n rows starting at row Y (very useful if you are calling from a web app that cannot cache large amounts of data). I've done a lot of testing on this, and once you go past the half-way point it is quicker to reverse the order by.

how to select nth row from a table

select rn, col_list from (select rownum rn, col_list from tab_name)
where rn=N;

where N is the number of the row you want.

SIMPLE WAY FIND THE Nth ROW IN A TABLE

SELECT MIN(salary)
  FROM (SELECT salary FROM employees ORDER BY salary DESC)
 WHERE ROWNUM <= 3;

The above query retrives the 3rd largest salary earning employee's salary.

n = the particular row you need

Nth row in a table by extending the rows X - Y query.

A little tweak to the query for "Can one retrieve only rows X to Y from a table?" will get you to the answer for selecting the Nth row in a table.

Query: Can one retrieve only rows X to Y from a table?
SQL: SELECT * FROM ( SELECT ROWNUM R, EMP.* FROM EMP WHERE ROWNUM < Y) WHERE R > X

Query: Can one retrieve only the Nth row from a table?
SQL: SELECT * FROM ( SELECT ROWNUM R, EMP.* FROM EMP) WHERE R = N;

With thanks,
Mahesh Konatham

SIMPLE WAY FIND THE Nth ROW IN A TABLE - with using distinct

Consider we need to retrieve the employee(s) details
who is getting 10th(n) best salary
------------------------------------------------------
select * from emp where sal=
(
select min(sal) as Salary from
(select distinct sal from emp order by sal desc) where rownum <= &n
)

- Faizal

One more way to find the Nth row from a table

select ename,sal from
(select ename,sal,DENSE_RANK() OVER(order by sal desc)Rank FROM emp)
where rank = 5;

Which Codd's Rule doesn't satisfies oracle database

Which Codd's Rule doesn't satisfy Oracle database?

Simple solution to select a row with ronum=n

Why didn't anyone suggest this:
select * from where rownum=n;

Because the all know that it

Because they all know that it won't work!