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.

Table of contents

1. SQL Query to Fetch Even and Odd Records
   1. Even Records from a Table
   2. Odd Records from a Table

SQL Query 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) ;

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.

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);

• The outer query selects all columns (*) from the table emp_1.
• It specifies a condition using the WHERE clause. The condition (ROWID, 0) IN (SELECT ROWID, MOD(ROWNUM, 2) FROM emp_1) filters the rows based on a comparison of (ROWID, 0) with the result of the subquery.
• The subquery (SELECT ROWID, MOD(ROWNUM, 2) FROM emp_1) selects the ROWID column and the result of the modulus operation MOD(ROWNUM, 2) on the ROWNUM column from the same emp_1 table.
• The main condition compares (ROWID, 0) for each row in the outer query with the results of the subquery. This checks if the combination of ROWID and 0 exists in the selected rows from the subquery.
• The overall effect is that the outer query will return all rows from emp_1 where the combination of ROWID and 0 is present in the selected rows from the subquery.

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);

• The outer query selects all columns (*) from the table emp_1.
• It specifies a condition using the WHERE clause. The condition (ROWID, 1) IN (SELECT ROWID, MOD(ROWNUM, 2) FROM emp_1) filters the rows based on a comparison of (ROWID, 1) with the result of the subquery.
• The subquery (SELECT ROWID, MOD(ROWNUM, 2) FROM emp_1) selects the ROWID column and the result of the modulus operation MOD(ROWNUM, 2) on the ROWNUM column from the same emp_1 table.
• The main condition compares (ROWID, 1) for each row in the outer query with the results of the subquery. This checks if the combination of ROWID and 1 exists in the selected rows from the subquery.
• The overall effect is that the query will retrieve rows from the emp_1 table where the ROWID and 1 combination is present in the selected rows from the subquery.