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

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.

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