OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Rownum

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rownum [message #560973]

Wed, 18 July 2012 01:16

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 ?

Report message to a moderator

Re: Rownum [message #560974 is a reply to message #560973]

Wed, 18 July 2012 01:24

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: Rownum [message #560976 is a reply to message #560974]

Wed, 18 July 2012 01:30

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

Report message to a moderator

Re: Rownum [message #560989 is a reply to message #560976]

Wed, 18 July 2012 02:47

Maaher
Messages: 7065
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

Report message to a moderator

Re: Rownum [message #560997 is a reply to message #560976]

Wed, 18 July 2012 03:38

satheesh_ss
Messages: 61
Registered: July 2012

Member

Thanks for your Answer Mr.Muralikri.....Its working

Report message to a moderator

Re: Rownum [message #561005 is a reply to message #560997]

Wed, 18 July 2012 04:27

sss111ind
Messages: 634
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);

Report message to a moderator

Re: Rownum [message #561006 is a reply to message #561005]

Wed, 18 July 2012 04:30

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: Rownum [message #561007 is a reply to message #561006]

Wed, 18 July 2012 04:39

_jum
Messages: 577
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;

Report message to a moderator

Re: Rownum [message #561009 is a reply to message #561007]

Wed, 18 July 2012 04:47

Michel Cadot
Messages: 68645
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

Maaher
Messages: 7065
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

Report message to a moderator

Re: Rownum [message #642886 is a reply to message #560976]

Tue, 22 September 2015 08:33

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

Report message to a moderator

Re: Rownum [message #642889 is a reply to message #642886]

Tue, 22 September 2015 08:44

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: Rownum [message #642892 is a reply to message #642886]

Tue, 22 September 2015 08:47

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

See documentation ROWNUM Pseudocolumn

Report message to a moderator

Re: Rownum [message #642895 is a reply to message #642889]

Tue, 22 September 2015 08:51

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

Report message to a moderator

Re: Rownum [message #642899 is a reply to message #560997]

Tue, 22 September 2015 09:01

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Previous Topic:	PLSQL_OPTIMIZE_LEVEL
Next Topic:	Calling Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 03:35:10 CDT 2024