# Can one retrieve only the Nth row from a table?

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

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.

»

- Login to post comments

## 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.

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

where N is the number of the row you want.

## SIMPLE WAY FIND THE Nth ROW IN A TABLE

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: where rownum=n;

select * from

## Because the all know that it

Because they all know that it won't work!