How to Write SQL Query to Get Even and Odd Records

Here’re SQL queries to fetch odd and even rows in Oracle. The Oracle database is the most popular across software projects.

SQL queries 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

Here’re two methods to select odd and even rows

SELECT * FROM EMP_1;

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

Method 1: SQL query to select 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 1: SQL query to select 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: SQL query to select even

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

Method 2: SQL query to select 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.