Home » Other » Training & Certification » even positioned rows
even positioned rows [message #257347] Wed, 08 August 2007 02:36 Go to next message
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

Re: even positioned rows [message #257350 is a reply to message #257347] Wed, 08 August 2007 02:44 Go to previous messageGo to next message
Noemi
Messages: 12
Registered: August 2007
Location: Hungary, Budapest
Junior Member

You can't use rnum in where.
Re: even positioned rows [message #257352 is a reply to message #257347] Wed, 08 August 2007 02:46 Go to previous messageGo to next message
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;
Re: even positioned rows [message #257353 is a reply to message #257352] Wed, 08 August 2007 02:49 Go to previous messageGo to next message
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?
Re: even positioned rows [message #257356 is a reply to message #257353] Wed, 08 August 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
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
Re: even positioned rows [message #257368 is a reply to message #257356] Wed, 08 August 2007 03:39 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
...and an order by
Re: even positioned rows [message #257411 is a reply to message #257347] Wed, 08 August 2007 05:20 Go to previous messageGo to next message
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
Re: even positioned rows [message #257417 is a reply to message #257411] Wed, 08 August 2007 05:38 Go to previous messageGo to next message
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
Re: even positioned rows [message #257422 is a reply to message #257417] Wed, 08 August 2007 05:49 Go to previous messageGo to next message
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
Re: even positioned rows [message #257429 is a reply to message #257422] Wed, 08 August 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
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
Re: even positioned rows [message #257445 is a reply to message #257429] Wed, 08 August 2007 06:52 Go to previous message
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
Previous Topic: Appex Training
Next Topic: interactive anonymous block
Goto Forum:
  


Current Time: Mon Dec 09 21:09:20 CST 2024