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:
- Write a subquery with an ORDER BY clause. Along with the data columns, select the pseudocolumn rownum with an alias, say rn.
- 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
{ 8 comments… read them below or add one }
Thank you.Nice..
how can u print even rows by this syntax which u r given above it absolutely wrong.
To display odd rows syntax is
select employee_id, first_name from employees where mod(employee_id, 2)=0;
To display even rows, syntax is
select employee_id, first_name from employees where mod(employee_id, 2)=1;
@KiranGayathri: You’re welcome.
@Surya: The question is about the position of rows in a result set, not the values of an id column.
Very well explained. Thank you very much.
This is wrong. In oracle it doesn’t select rownum=2 . Try it.
EVEN:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
ODD :
EVEN:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
HI,
On the above discussion, now how to print all even rows at first and all odd rows next using sql queries.
THANK UU SO MUCH