OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » even positioned rows

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

even positioned rows [message #257347]

Wed, 08 August 2007 02:36

tarunj
Messages: 23
Registered: April 2007
Location: Noida

Junior Member

I have a table "mycd" which has one column name.

name
---------
David
Jimmy
Sam
Jethro
William

Now I want all the even positioned rows.

I tried:--

select name,mod(rownum,2) as rnum from mycd where rnum=0;

It throws the following error:-
ORA-00904: "rnum": invalid identifier

Report message to a moderator

Re: even positioned rows [message #257350 is a reply to message #257347]

Wed, 08 August 2007 02:44

Noemi
Messages: 12
Registered: August 2007
Location: Hungary, Budapest

Junior Member

You can't use rnum in where.

Report message to a moderator

Re: even positioned rows [message #257352 is a reply to message #257347]

Wed, 08 August 2007 02:46

Noemi
Messages: 12
Registered: August 2007
Location: Hungary, Budapest

Junior Member

select name,mod(rownum,2) as rnum
from mycd where mod(rownum,2)=0;

Report message to a moderator

Re: even positioned rows [message #257353 is a reply to message #257352]

Wed, 08 August 2007 02:49

tarunj
Messages: 23
Registered: April 2007
Location: Noida

Junior Member

select name,mod(rownum,2) as rnum from mycd where mod(rownum,2)=0;

gives no result.

Why i can't use rnum in where clause?
is there any other way?

Report message to a moderator

Re: even positioned rows [message #257356 is a reply to message #257353]

Wed, 08 August 2007 02:53

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

rownum is a number given to each row in the result set.
The first number is 1.
But you don't want to select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
So the next one should have the number 1 but you don't select it.
...

You have to first numbered then select.
Use a subquery.

Regards
Michel

Report message to a moderator

Re: even positioned rows [message #257368 is a reply to message #257356]

Wed, 08 August 2007 03:39

Frank
Messages: 7901
Registered: March 2000

Senior Member

...and an order by

Report message to a moderator

Re: even positioned rows [message #257411 is a reply to message #257347]

Wed, 08 August 2007 05:20

muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh

Senior Member

select name
from
(
select rownum aa,name from mycd
) p
where mod(p.aa,2) =0

Report message to a moderator

Re: even positioned rows [message #257417 is a reply to message #257411]

Wed, 08 August 2007 05:38

Maaher
Messages: 7065
Registered: December 2001

Senior Member

muzahidul islam, that's just selecting half of the table. And the rows you select are random rows. No order by.

MHE

Report message to a moderator

Re: even positioned rows [message #257422 is a reply to message #257417]

Wed, 08 August 2007 05:49

Noemi
Messages: 12
Registered: August 2007
Location: Hungary, Budapest

Junior Member

select name
from
(select name, mod(rownum,2) b
from mycd) a
where b=0
order by name

Report message to a moderator

Re: even positioned rows [message #257429 is a reply to message #257422]

Wed, 08 August 2007 06:24

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The order should be put in the SUB-query and not (only) the OUTER one.

Regards
Michel

Report message to a moderator

Re: even positioned rows [message #257445 is a reply to message #257429]

Wed, 08 August 2007 06:52

Maaher
Messages: 7065
Registered: December 2001

Senior Member

A short reminder: if you talk about even and odd rows, you talk about order. You want your result set ordered. It is up to you, tarunj, to tell Oracle how you want your rows sorted. Can you tell us how you want to sort the result set? Once we cleared that, we can talk about even and odd.

MHE

Report message to a moderator

Previous Topic:	Appex Training
Next Topic:	interactive anonymous block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 06:39:57 CST 2024