Fetch odd or even rows from table in Oracle

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.