How to get the 25th row of a table.

Showing Answers 1 - 33 of 33 Answers

AHMED BIN SAYEED

  • Dec 21st, 2006
 

Hi,

to get the 25th row of a table the following query can be useful

SELECT * FROM (SELECT ROWNUM r,e.* FROM emp e)

WHERE r = 25

Pradhan

  • Jan 3rd, 2007
 

We can use this query , too:

select * from Emp where rownum < 26

minus

select * from Emp where rownum<25

raghav_sy

  • Jan 17th, 2007
 

I will share few things regarding the solutions given for this problem,?among all only? two queries will work given by Ahmed and Pradhan.

But among these two, Ahmed's is the best solution. because its a very much optimized query.

Query given by gangadharan will not solve this problem, as it is used for Nth max. analysis.

Query given by shankar will not work, because we can not use keyword ROWNUM with Equality(=).

Well one thing to note here is that, even in Ahmed's query, r is the alias for ROWNUM. and later r has been used with equality.=).

So remember, ROWNUM can not be used with equality but after aliasing it equality condition can be used.

and i request my frineds here, post solution and also explain a bit how its working, because how is very important as compared to what.

Prasanna Kumar

  • Feb 22nd, 2007
 

select * from emp where rowid=(select max(rowid) from emp where rownum<26)

  Was this answer useful?  Yes

hai

why equality condition does'nt work with rownum, i'm giving my explanation here

the query
select * from emp where rownum=25;

it fetches first record makes it rownum 1 as it does'nt match the where criteria, the record discarded

it fetches 2nd record makes it rownum 1 again
that is why rownum=x does'nt work

Art11

  • Sep 27th, 2011
 

You'd better use analytic functions, so your managers would think high of you. Besides, those fn-s are smart and fast.
I would not user ROWNUM with ORDER BY as in some examples I see here... THe ORDER BY will reorder the rows... This is my personal preference.

Code
  1. SELECT * FROM ( SELECT deptno, ename, sal

  2.   , ROW_NUMBER() OVER (ORDER BY sal DESC, ename) AS row_num

  3.   , RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank

  4.   , DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) d_rank FROM emp )

  5. WHERE row_num = 4

  6. /

  Was this answer useful?  Yes

Anonymus

  • Oct 10th, 2011
 

Hi raghav_sy

Please check before commenting others mistake ahmed's query is perfect.

  Was this answer useful?  Yes

mohan

  • Oct 14th, 2011
 

SELECT * FROM TABLE_NAMEWHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions