How to Write SQL Query to Get Even and Odd Records

Here are SQL queries to find even and odd records from a table. These were tested in the Oracle database since it is popular in software applications.

IN THIS PAGE

  1. How to fetch even and odd records
    1. How to fetch even records from a table
    2. How to fetch odd records from a table

How to fetch even and odd records

Create table

CREATE TABLE EMP_1 (EMPNAME CHAR(20), EMPID INT);

Insert values

INSERT INTO EMP_1 VALUES('SRINIVAS', 12345); 
INSERT INTO EMP_1 VALUES('RANI', 55667); 
INSERT INTO EMP_1 VALUES('VENKAT', 89112) ; 
INSERT INTO EMP_1 VALUES('TUFFAN', 88999) ;
Advertisements

Give a SELECT query to see the inserted rows.

SELECT * FROM EMP_1;

EMPNAME	                EMPID
SRINIVAS            	12345
RANI                	55667
VENKAT              	89112
TUFFAN              	88999
4 rows selected.

Mindset of disrupter!
Rather than observing a problem and sitting back and waiting for others to solve it, they jumped in head first, seeing massive problems as massive opportunities. To adopt the mindset of a disruptor, you must first disrupt yourself.

How to fetch even records from a table

Method 1: Selecting even rows

select * from
  (select empname, empid, rownum rn
  from emp_1
  order by empid)
  where  mod (rn, 2) = 0;

EMPNAME	                EMPID	RN
RANI                	55667	2
TUFFAN              	88999	4

Method 2: Selecting even rows

select * from emp_1 
where (rowid,0) 
in(select rowid, mod(rownum,2) 
from emp_1);

How to fetch odd records from a table

Method 1: Selecting odd rows

select * from
  (select empname, empid, rownum rn
  from emp_1
  order by empid)
  where  mod (rn, 2) <> 0;

EMPNAME	                EMPID	RN
SRINIVAS            	12345	1
VENKAT              	89112	3

Method 2: Selecting odd rows

select * from emp_1 
where (rowid,1) 
in(select rowid,mod(rownum,2) 
from emp_1);

More Srinimf

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.