Home » SQL & PL/SQL » SQL & PL/SQL » To get alternative row from table
To get alternative row from table [message #192194] Mon, 11 September 2006 07:21 Go to next message
gauravpuri
Messages: 26
Registered: August 2005
Location: Chennai
Junior Member

hi

How to get alternative rows from table.

I have used the following query but why it is not working???

select * from emp
where 0 in (select mod(rownum,2) from emp);


Re: To get alternative row from table [message #192201 is a reply to message #192194] Mon, 11 September 2006 07:34 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Try these:

Alternate rows starting from the first one:

SELECT *
FROM (SELECT rownum row_num,emp.* FROM emp)
WHERE MOD(row_num,2) <> 0

Alternate rows starting from the second one:

SELECT *
FROM (SELECT rownum row_num,emp.* FROM emp)
WHERE MOD(row_num,2) = 0


Regards,

Liza
Re: To get alternative row from table [message #192205 is a reply to message #192194] Mon, 11 September 2006 07:44 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi

SELECT * FROM (SELECT ROWNUM rno, EMP.* FROM EMP) e
WHERE MOD(e.rno,2) <> 0;


with Regards
Venkat
icon10.gif  Re: To get alternative row from table [message #192209 is a reply to message #192201] Mon, 11 September 2006 07:49 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Huh,

I am(my net connection took a long way to submit Cool ) little bit slower this time.

Venkat.
Re: To get alternative row from table [message #192212 is a reply to message #192209] Mon, 11 September 2006 07:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And I was too fast Very Happy

MHE
Re: To get alternative row from table [message #192217 is a reply to message #192194] Mon, 11 September 2006 07:55 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Yes Maaher, You are too fast absolutely correct, but I am not tried to repeat the previous replies..
Re: To get alternative row from table [message #192223 is a reply to message #192194] Mon, 11 September 2006 08:24 Go to previous messageGo to next message
gauravpuri
Messages: 26
Registered: August 2005
Location: Chennai
Junior Member

thanks 4 urs reply

but why following query gives all rows

select *
from emp
where 0 in (select mod(rownum,2) from emp)


Regards
gaurav
Re: To get alternative row from table [message #192232 is a reply to message #192223] Mon, 11 September 2006 08:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
For each row, Oracle will evaluate whether the 0 numeric literal is one of the possible values of
select mod(rownum,2) from emp
If there is one row that satisfies this condition (i.e. there is one row that has mod(rownum,2) = 0) the record is accepted. If your emp table has at least 2 records, the rows will all be selected since the retrieved record with rownum 2 will result in mod(rownum,2) = 0.

MHE
Re: To get alternative row from table [message #192234 is a reply to message #192223] Mon, 11 September 2006 08:55 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Because, your "where cluase" that you coded, will always yield true.
that is, your subquery will return 0 for alternative rows. So the conditions would hold true.
select *
from emp
where 0 in (1,0,1.....)

Thanks,
Thangam


Previous Topic: Query Needed
Next Topic: Finding Tables with similar columns
Goto Forum:
  


Current Time: Fri Dec 09 17:36:12 CST 2016

Total time taken to generate the page: 0.45855 seconds