i'm using below query to retrieve even numbered records.but in reslut it is displaying no rows
select ename,job from emp where mod(rownum,2)=0;
why mod(rownum,2) is not working in where conditiom
can u please also give the query to select the odd number records
Is there anything wrong with that query?
suggetions please..
Best Answer
It is not working because: for the first row
ROWNUM
is1
and, in this case,MOD(ROWNUM,2)
is1
and since yourWHERE
statement isMOD(ROWNUM,2)=0
then this reduces to1=0
and the row is discarded. The subsequent row will then be tested against aROWNUM
of 1 (since the previous row is no longer in the output and will not have a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail theWHERE
test and are discarded.If you try to get the odd rows this way using
WHERE MOD(ROWNUM,2)=1
then it will return the first row only and the second, and subsequent, rows will fail the test and will never be included in the query.As Vijaykumar Hadalgi suggests, you need to select the ROWNUM in a sub-query (where it can number all the rows without a where clause to restrict it) and then in the outer query perform the test to restrict the rows:
SQLFIDDLE