Question: How can I select only the even/odd rows from an Oracle table?
Answer: Even or odd rows are 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 the SCOTT emp table: let’s say my result set is ordered by employee numbers.
Odd Rows
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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 137 rows selected. |
Even Rows
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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 147 rows selected. |
No comments:
Post a Comment