sql - how to select even records from a table in oracle?

Question

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..

Answer 1

It is not working because: for the first row ROWNUM is 1 and, in this case, MOD(ROWNUM,2) is 1 and since your WHERE statement is MOD(ROWNUM,2)=0 then this reduces to 1=0 and the row is discarded. The subsequent row will then be tested against a ROWNUM 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 the WHERE 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:

SELECT ename, job
FROM   (
  SELECT ename,
         job,
         ROWNUM AS row_id             -- Generate ROWNUM second.
  FROM   (
    SELECT ename, job
    FROM   Emp
    ORDER BY ename                    -- ORDER BY first.
  )
)
WHERE  MOD( row_id, 2 ) = 0;          -- Filter third.

SQLFIDDLE