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: 63384
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: 7062
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: 554
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: 63384
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: 508
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: 63384
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 messageGo to next message
Maaher
Messages: 7062
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
Re: Rownum [message #642886 is a reply to message #560976] Tue, 22 September 2015 08:33 Go to previous messageGo to next message
battaarchana
Messages: 8
Registered: September 2015
Junior Member
muralikri wrote on Wed, 18 July 2012 13:30
Try like...

select * from (select rownum rn ,e.* from emp e) where rn between 1 and 3 


Hi,

Why it doesnt work with below SQL statement?

SELECT ROWNUM RN, e.* from emp e where RN BETWEEN 1 AND 3.

why should we use two SELECT statements here??

B Archana
Re: Rownum [message #642889 is a reply to message #642886] Tue, 22 September 2015 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 12223
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually that one will work, but this one won't:
SELECT ROWNUM RN, e.* from emp e where RN BETWEEN 2 AND 3


It's because of the point at which rownum is assigned - It's assigned after the row is fetched from the table and compared to the where clause. You can't get a row with rownum = 2 until you've successfully selected a row with rownum = 1. Having an inner select allows that to happen.

Also, to re-iterate what Maaher pointed out above - a query that restricts by rownum that doesn't have an order by clause makes no sense - you're just selecting random rows unless you add order by.
Re: Rownum [message #642892 is a reply to message #642886] Tue, 22 September 2015 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63384
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See documentation ROWNUM Pseudocolumn

Re: Rownum [message #642895 is a reply to message #642889] Tue, 22 September 2015 08:51 Go to previous messageGo to next message
battaarchana
Messages: 8
Registered: September 2015
Junior Member
cookiemonster wrote on Tue, 22 September 2015 20:44
Actually that one will work, but this one won't:
SELECT ROWNUM RN, e.* from emp e where RN BETWEEN 2 AND 3


It's because of the point at which rownum is assigned - It's assigned after the row is fetched from the table and compared to the where clause. You can't get a row with rownum = 2 until you've successfully selected a row with rownum = 1. Having an inner select allows that to happen.

Also, to re-iterate what Maaher pointed out above - a query that restricts by rownum that doesn't have an order by clause makes no sense - you're just selecting random rows unless you add order by.



Thank you @cookiemonster. I see your point.

Thanks & Regards,
B Archana
Re: Rownum [message #642899 is a reply to message #560997] Tue, 22 September 2015 09:01 Go to previous message
Solomon Yakobson
Messages: 2418
Registered: January 2010
Location: Connecticut, USA
Senior Member
satheesh_ss wrote on Wed, 18 July 2012 04:38
Thanks for your Answer Mr.Muralikri.....Its working


You didn't provide Oracle version. Oracle 12C added new row limiting feature (although under the hood it is still same old ROWNUM + nesting similar to Muralikri's solution):

SELECT  *
  FROM  your_table
  OFFSET 99 ROWS FETCH NEXT 21 ROWS ONLY;


However, and many already noted that, without ORDER BY the above query is not deterministic and can return different set of 21 rows next time you run it.

SY.
Previous Topic: PLSQL_OPTIMIZE_LEVEL
Next Topic: Calling Procedure
Goto Forum:
  


Current Time: Sat Jul 02 02:54:21 CDT 2016

Total time taken to generate the page: 0.04809 seconds