Home » SQL & PL/SQL » SQL & PL/SQL » can i find nth row for particular table
icon2.gif  can i find nth row for particular table [message #229860] Tue, 10 April 2007 03:28 Go to next message
rajaindia02
Messages: 2
Registered: April 2007
Location: india
Junior Member
Hi,

i am new to this forum

i have one question

can i find nth row of particular table?

is it possible?

Re: can i find nth row for particular table [message #229879 is a reply to message #229860] Tue, 10 April 2007 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No as there is no nth row in a table unless you give an order.

Regards
Michel
Re: can i find nth row for particular table [message #229883 is a reply to message #229860] Tue, 10 April 2007 04:13 Go to previous messageGo to next message
somu4
Messages: 1
Registered: January 2007
Location: KOLKATA
Junior Member

Through this query you can find a nth row of emp table.
Pls try it.

SELECT * FROM EMP a WHERE 5 =
(SELECT COUNT(ROWID) FROM EMP b WHERE a.ROWID >= b.ROWID)
Re: can i find nth row for particular table [message #229884 is a reply to message #229883] Tue, 10 April 2007 04:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
somu4 wrote on Tue, 10 April 2007 11:13
Through this query you can find a nth row of emp table.
Pls try it.

SELECT * FROM EMP a WHERE 5 =
(SELECT COUNT(ROWID) FROM EMP b WHERE a.ROWID >= b.ROWID)

There you have it: "A" nth row. It is a random row, just like any other. ROWID has nothing to do with order. It is the programmer that defines the order. That's final, no "if", no "but"...

MHE
Re: can i find nth row for particular table [message #229888 is a reply to message #229883] Tue, 10 April 2007 04:32 Go to previous messageGo to next message
rajaindia02
Messages: 2
Registered: April 2007
Location: india
Junior Member
thanks a lot
Re: can i find nth row for particular table [message #230038 is a reply to message #229888] Tue, 10 April 2007 13:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
This works also:
select * from emp where rownum <= 1;

Re: can i find nth row for particular table [message #230042 is a reply to message #230038] Tue, 10 April 2007 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hesitated to post this one.
I thought this would be feeled too harsh. Wink

Regards
Michel
Re: can i find nth row for particular table [message #230062 is a reply to message #229860] Tue, 10 April 2007 15:41 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
if you wanted to get the fifth row after sorting by emp_number (for example), you could do something like


select *
from emp e,
(select x.emp_number,rownum rnum
 from
 (select emp_number from emp
  order by emp_number) x) y
where y.emp_number = e.emp_number
and y.rnum = 5;
Re: can i find nth row for particular table [message #230063 is a reply to message #230062] Tue, 10 April 2007 15:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very weird statement!
Why this join?

Regards
Michel

Re: can i find nth row for particular table [message #230065 is a reply to message #229860] Tue, 10 April 2007 15:53 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
inner select sorts by order. The next select generates the rownum, which is generated before the order. and the outer select returns finds the fifth employee number and select the entire row from the emp table that matches the first emp number.
Re: can i find nth row for particular table [message #230067 is a reply to message #230065] Tue, 10 April 2007 15:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is why do you just select emp_number and not all columns in the inner select and avoid the join. Your "emp e" is only there to get all columns from emp_number.

Regards
Michel

Re: can i find nth row for particular table [message #230070 is a reply to message #229860] Tue, 10 April 2007 16:06 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
speeds up the query if the inner selects do not have to maintain and handle all the additional columns. And since emp_number is indexed, it never has to look at the actual table until the final join.
Re: can i find nth row for particular table [message #230072 is a reply to message #230070] Tue, 10 April 2007 16:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case why not using rowid, it is even faster?

Regards
Michel
Re: can i find nth row for particular table [message #230075 is a reply to message #229860] Tue, 10 April 2007 16:21 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
true enough.... Nice catch


select *
from emp e,
(select x.emp_number,x.row_id,rownum rnum
 from
 (select emp_number,rowid row_id from emp
  order by emp_number) x) y
where y.row_id = e.rowid
and y.rnum = 5;

[Updated on: Tue, 10 April 2007 16:23]

Report message to a moderator

Re: can i find nth row for particular table [message #230149 is a reply to message #230075] Wed, 11 April 2007 01:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Bill B wrote on Tue, 10 April 2007 23:21
true enough.... Nice catch


select *
from emp e,
(select x.emp_number,x.row_id,rownum rnum
 from
 (select emp_number,rowid row_id from emp
  order by emp_number) x) y
where y.row_id = e.rowid
and y.rnum = 5;



ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.

MHE
Re: can i find nth row for particular table [message #230161 is a reply to message #230149] Wed, 11 April 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't follow you:
SQL> select *
  2  from emp e,
  3  (select x.empno,x.row_id,rownum rnum
  4   from
  5   (select empno,rowid row_id from emp
  6    order by empno) x) y
  7  where y.row_id = e.rowid
  8  and y.rnum = 5
  9  /
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO      EMPNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----------
ROW_ID                   RNUM
------------------ ----------
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30       7654
AAAMTdAAEAAAAAkAAE          5

1 row selected.

Regards
Michel
Re: can i find nth row for particular table [message #230171 is a reply to message #230161] Wed, 11 April 2007 02:24 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 11 April 2007 09:03
Don't follow you
I must wake up. I probably mistyped. Here's how I can reproduce the error I got. Notice the row_id that has been changed into rowid. Laughing
In the mean time I got me a nice cup of coffee so I'll probably wake up in a couple of minutes.

select *
from emp e,
(select x.empno,x.rowid,rownum rnum
 from
 (select empno,rowid row_id from emp
  order by empno) x) y
where y.rowid = e.rowid
and y.rnum = 5;


Silly me. Anyway, if I look at the plans I'd probably opt for another method, but then again: without the details it's hard to make a stand.

SELECT *
FROM   emp e
     , (SELECT x.empno
             , x.row_id
             , ROWNUM rnum
        FROM   (SELECT   empno
                       , ROWID row_id
                FROM     emp
                ORDER BY empno) x) y
WHERE  y.row_id = e.ROWID AND y.rnum = 5
/

Plan
SELECT STATEMENT  CHOOSE
Cost: 5  Bytes: 1,106  Cardinality: 14  					
	6 HASH JOIN  Cost: 5  Bytes: 1,106  Cardinality: 14  				
		1 TABLE ACCESS FULL SCOTT.EMP Cost: 2  Bytes: 644  Cardinality: 14  			
		5 VIEW SCOTT. Cost: 2  Bytes: 462  Cardinality: 14  			
			4 COUNT  		
				3 VIEW SCOTT. Cost: 2  Bytes: 280  Cardinality: 14  	
					2 INDEX FULL SCAN UNIQUE SCOTT.EMP_PRIMARY_KEY Cost: 1  Bytes: 140  Cardinality: 14  



SELECT *
FROM   (SELECT empno
             , ename
             , job
             , mgr
             , hiredate
             , sal
             , comm
             , deptno
             , RANK () OVER (ORDER BY empno) rnum
        FROM   emp) x
WHERE  x.rnum = 5;

Plan
SELECT STATEMENT  CHOOSE
Cost: 2  Bytes: 1,54  Cardinality: 14  				
	4 VIEW SCOTT. Cost: 2  Bytes: 1,54  Cardinality: 14  			
		3 WINDOW NOSORT  Cost: 2  Bytes: 546  Cardinality: 14  		
			2 TABLE ACCESS BY INDEX ROWID SCOTT.EMP Cost: 2  Bytes: 546  Cardinality: 14  	
				1 INDEX FULL SCAN UNIQUE SCOTT.EMP_PRIMARY_KEY Cost: 1  Cardinality: 14 


MHE

[Updated on: Wed, 11 April 2007 02:35]

Report message to a moderator

Previous Topic: Basic scripts for select statement
Next Topic: read from a structure
Goto Forum:
  


Current Time: Sat Dec 10 13:02:37 CST 2016

Total time taken to generate the page: 0.07621 seconds