Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table?

Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query.

Once the order is specified, then the query to retrieve odd rows or even rows can be written in this form:

1. Write a subquery with an ORDER BY clause. Along with the data columns, select the pseudocolumn rownum with an alias, say rn.
2. In the outer query, reference the alias rn and use the mod function to get odd rows or even rows.

An example using SCOTT schema‘s emp table: let’s say my result set is ordered by employee numbers.

Odd Rows

SQL> select * from
  2  (select empno, ename, sal, rownum rn
  3  from emp
  4  order by empno)
  5  where  mod (rn, 2) <> 0;

     EMPNO ENAME             SAL         RN
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7521 WARD             1250          3
      7654 MARTIN           1250          5
      7782 CLARK            2850          7
      7839 KING             5000          9
      7876 ADAMS            1100         11
      7902 FORD             3000         13

7 rows selected.

Even Rows

SQL> select * from
  2  (select empno, ename, sal, rownum rn
  3  from emp
  4  order by empno)
  5  where  mod (rn, 2) = 0;

     EMPNO ENAME             SAL         RN
---------- ---------- ---------- ----------
      7499 ALLEN            1600          2
      7566 JONES            2975          4
      7698 BLAKE            2850          6
      7788 SCOTT            3000          8
      7844 TURNER           1500         10
      7900 JAMES             950         12
      7934 MILLER           1300         14

7 rows selected.

Photo by tuchodi