OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » % with ROWNUM (SQL)

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

% with ROWNUM [message #642888]

Tue, 22 September 2015 08:43

battaarchana
Messages: 8
Registered: September 2015

Junior Member

Hi,

I am working with Oracle database.

I can use ROWNUM as "select * from mytab where rownum <= 5". But can we also use % sign with ROWNUM? If yes, can you give me an example.
I tried below which didnt work. Sad

select * from mytab where rownum % 5
select * from mytab where rownum 5%
select * from mytab where rownum=5%
select * from mytab where rownum='5%'

Thanks & Regards,
B Archana.

Report message to a moderator

Re: % with ROWNUM [message #642891 is a reply to message #642888]

Tue, 22 September 2015 08:45

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

No. I've no idea what you think % is supposed to do there.

Report message to a moderator

Re: % with ROWNUM [message #642893 is a reply to message #642888]

Tue, 22 September 2015 08:48

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

like this?

orclz>
orclz> select * from scott.dept fetch first 50 percent rows only;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

orclz>

Report message to a moderator

Re: % with ROWNUM [message #642896 is a reply to message #642893]

Tue, 22 September 2015 08:52

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you're going to show off 12c features John you should probably make it clear that's what you're doing. Most people aren't on that version yet.
The sample keyword does the same thing in older versions.

Report message to a moderator

Re: % with ROWNUM [message #642897 is a reply to message #642891]

Tue, 22 September 2015 08:54

battaarchana
Messages: 8
Registered: September 2015

Junior Member

cookiemonster wrote on Tue, 22 September 2015 20:45

No. I've no idea what you think % is supposed to do there.

I mean, i dont want to mention any specific number of rows with ROWNUM. I want to see some percentage of rows, like half rows by saying 50%.

Thanks,
B Archana

Report message to a moderator

Re: % with ROWNUM [message #642898 is a reply to message #642893]

Tue, 22 September 2015 08:57

battaarchana
Messages: 8
Registered: September 2015

Junior Member

John Watson wrote on Tue, 22 September 2015 20:48

like this?

orclz>
orclz> select * from scott.dept fetch first 50 percent rows only;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

orclz>

Hi John,

It didnt work for me. It says SQL command not properly ended.

Thanks,
B Archana

Report message to a moderator

Re: % with ROWNUM [message #642900 is a reply to message #642898]

Tue, 22 September 2015 09:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

battaarchana wrote on Tue, 22 September 2015 06:57

John Watson wrote on Tue, 22 September 2015 20:48
like this?
orclz>
orclz> select * from scott.dept fetch first 50 percent rows only;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

orclz>
Hi John,

It didnt work for me. It says SQL command not properly ended.

Thanks,
B Archana

Obviously you are NOT using V12 DB & John is/was.

Report message to a moderator

Re: % with ROWNUM [message #642902 is a reply to message #642900]

Tue, 22 September 2015 09:18

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

That will (hopefully) teach OP posting Oracle version when asking a question.

SY.

Report message to a moderator

Re: % with ROWNUM [message #642903 is a reply to message #642896]

Tue, 22 September 2015 10:06

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

cookiemonster wrote on Tue, 22 September 2015 14:52

If you're going to show off 12c features John you should probably make it clear that's what you're doing. Most people aren't on that version yet.
The sample keyword does the same thing in older versions.

I don't understand it. 11.2 has been out of premier support since January; 12.1 was released in July two years ago; and we still have to assume that 12.x is not being used. I've been getting all our clients onto the current release. Upgrade is easy nowadays.

@B Archana, have you looked up the SAMPLE clause yet? It is less predictable, and (I suspect) less efficient because it doesn't use analytic functions.

Report message to a moderator

Re: % with ROWNUM [message #642904 is a reply to message #642903]

Tue, 22 September 2015 10:29

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

Senior Member
Account Moderator

Quote:

Upgrade is easy nowadays.

Not so easy when you have software vendors who tell you they have not certified 12c and even some that have not certified 11.2 yet!
Even with internal software most of the time, for application manager, testing the new Oracle version is far after integrating application new features or improving current ones.
When I say "far after" is when Oracle no more supports in any way the version, that is 10.2 in these days, not 11.2.

Report message to a moderator

Re: % with ROWNUM [message #642955 is a reply to message #642903]

Wed, 23 September 2015 07:31

battaarchana
Messages: 8
Registered: September 2015

Junior Member

cookiemonster wrote on Tue, 22 September 2015 14:52

@B Archana, have you looked up the SAMPLE clause yet? It is less predictable, and (I suspect) less efficient because it doesn't use analytic functions.

Hi John,

Yes, SAMPLE clause worked for me and i see that Number of records it returns is always variable. Besides to SAMPLE, i also tried to fetch exact percentage of records using below query which worked too. I found what i was looking for. Thanks to everyone for your help. Smile

select * from employees where rownum <= (select count(*)*0.5 from employees)

Thanks & Regards,
B Archana

Report message to a moderator

Re: % with ROWNUM [message #642956 is a reply to message #642955]

Wed, 23 September 2015 07:39

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

Senior Member
Account Moderator

Or scanning the table only once:

select * 
from (select e.*, 
             row_number() over (order by null) rn, 
             count(*) over () cnt 
      from emp e)
where rn <= cnt/2
/

[Updated on: Wed, 23 September 2015 07:40]

Report message to a moderator

Previous Topic:	Calling Procedure
Next Topic:	Using Procedure/Function in a Select Statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 08:33:05 CDT 2024