Can one retrieve only the Nth row from a table?

Submitted by admin on Wed, 2005-12-14 23:31
Body: 

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.

»

Comments

Submitted by JulesLt (not verified) on Wed, 2006-01-11 12:32.

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.

»

Submitted by samar bijaya panda (not verified) on Tue, 2006-02-21 05:56. 

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.

»

Submitted by Prakash (not verified) on Mon, 2006-04-17 01:35. 

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

»

Submitted by Mahesh Konatham (not verified) on Thu, 2006-06-08 07:26.

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

»

Submitted by ahamed79 on Fri, 2007-03-02 19:44.

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

»

Submitted by chaitanya1234@r... on Thu, 2007-03-22 01:17.

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

»

Submitted by jeetendra.guddu on Thu, 2007-03-22 04:41.

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

»

Submitted by Abhijat on Wed, 2011-11-23 14:17.

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

»

Submitted by Frank Naude on Thu, 2011-11-24 03:54.

Because they all know that it won't work!

»