For Fetch odd or even rows / odd or even on base of any column value in Oracle table.
We use HR schema for showing the odd or even rows from employees table. Employees table has 107 rows and employee_id start from 100 value. We use filter on employee_id <= 110 to get only less output which simplify to show in following example.
For Even Rows ( We use Mod function and rownum. Mod value should be zero).
select * from
(select employee_id, first_name, salary, rownum rn
from employees where employee_id <= 110
order by employee_id)
where mod (rn, 2) = 0;
EMPLOYEE_ID FIRST_NAME SALARY RN
----------- -------------------- ---------- ----------
101 Neena 17000 2
103 Alexander 9000 4
105 David 4800 6
107 Diana 4200 8
109 Daniel 9000 10
For Odd rows ( use rownum function, Mod value should be not Zero)
select * from
(select employee_id, first_name, salary, rownum rn
from employees where employee_id <= 110
order by employee_id)
where mod (rn, 2) <> 0;
EMPLOYEE_ID FIRST_NAME SALARY RN
----------- -------------------- ---------- ----------
100 Steven 24000 1
102 Lex 17000 3
104 Bruce 6000 5
106 Valli 4800 7
108 Nancy 12008 9
110 John 8200 11
Fetch odd or even on basis of Column value as follows:
--Fetch EVEN row on Employee_id column from HR schema.
select employee_id, first_name, salary
from employees where employee_id <= 110
and mod(employee_id,2) = 0
order by employee_id;
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
100 Steven 24000
102 Lex 17000
104 Bruce 6000
106 Valli 4800
108 Nancy 12008
110 John 8200
--Fetch ODD row on Employee_id column for HR Schema
select employee_id, first_name, salary
from employees where employee_id <= 110
and mod(employee_id,2) <> 0
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
101 Neena 17000
103 Alexander 9000
105 David 4800
107 Diana 4200
109 Daniel 9000
order by employee_id;