Home » SQL & PL/SQL » SQL & PL/SQL » Rownum
Rownum [message #560973] Wed, 18 July 2012 01:16 Go to next message
satheesh_ss
Messages: 61
Registered: July 2012
Member
A table has 150 records. How do you retrieve 100th row to 120th row from that table ?
Re: Rownum [message #560974 is a reply to message #560973] Wed, 18 July 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rows have no number in a table.
Rownum is explained in Database SQL Reference
Read the books we pointed you to.
Feedback and thank people that answer you.

Regards
Michel
Re: Rownum [message #560976 is a reply to message #560974] Wed, 18 July 2012 01:30 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like...

select * from (select rownum rn ,e.* from emp e) where rn between 1 and 3 
Re: Rownum [message #560989 is a reply to message #560976] Wed, 18 July 2012 02:47 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
Unless you provide an order, that question does not make sense. Key is that you sort your result set and only then you can limit it. It is you who decides what is "first" or "last". For Oracle, it is just a set of rows.

MHE
Re: Rownum [message #560997 is a reply to message #560976] Wed, 18 July 2012 03:38 Go to previous messageGo to next message
satheesh_ss
Messages: 61
Registered: July 2012
Member
Thanks for your Answer Mr.Muralikri.....Its working
Re: Rownum [message #561005 is a reply to message #560997] Wed, 18 July 2012 04:27 Go to previous messageGo to next message
sss111ind
Messages: 507
Registered: April 2012
Location: India
Senior Member


SELECT *
  FROM emp
 WHERE empno IN (SELECT empno
                   FROM (SELECT empno, ROWNUM rn
                           FROM (  SELECT empno
                                     FROM emp
                                 ORDER BY sal DESC))
                  WHERE rn BETWEEN 1 AND 3);
Re: Rownum [message #561006 is a reply to message #561005] Wed, 18 July 2012 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why something so complex? Just
SELECT *
FROM (SELECT e.*, ROWNUM rn
      FROM (SELECT * FROM emp ORDER BY sal DESC) e)
WHERE rn BETWEEN 1 AND 3;

Regards
Michel
Re: Rownum [message #561007 is a reply to message #561006] Wed, 18 July 2012 04:39 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
or with analytic function only two SELECT:
SELECT *
  FROM
   (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) rn, e.*  FROM emp e)
 WHERE rn BETWEEN 1 AND 3; 
Re: Rownum [message #561009 is a reply to message #561007] Wed, 18 July 2012 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the title is "rownum".
(Note that one SELECT less does not mean faster.)

If you want to be complete then you have to make reference to the 3 numbering functions: ROW_NUMBER, RANK and to DENSE_RANK functions (and to http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post for an example). Smile

Regards
Michel

[Updated on: Wed, 18 July 2012 04:48]

Report message to a moderator

Re: Rownum [message #561014 is a reply to message #560997] Wed, 18 July 2012 04:58 Go to previous message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
satheesh_ss wrote on Wed, 18 July 2012 10:38
Thanks for your Answer Mr.Muralikri.....Its working
It isn't better than:
select rownum rn, e.* from emp e where rownum <= 3
There's no order by, so there's no telling what records Oracle will return. The only thing you can say is that it will return three rows (in our example).

MHE
Previous Topic: Package & Procedure
Next Topic: Help to Create this table with the following structure
Goto Forum:
  


Current Time: Sat Dec 20 17:59:58 CST 2014

Total time taken to generate the page: 0.08146 seconds