Using Mod Function

Topics covered:

• Display alternate rows
• Display even-numbered rows
• Display odd-numbered rows
• Display even rows first and odd rows second

1. Write a query to display alternate rows

Answer:

SELECT *
FROM (
    SELECT empno, ename, ROWNUM rn
    FROM emp
)
WHERE MOD(rn, 2) = 1;

Or:

SELECT *
FROM (
    SELECT empno, ename, ROWNUM rn
    FROM emp
)
WHERE MOD(rn, 2) = 0;

The first query returns rows 1, 3, 5, and so on. The second query returns rows 2, 4, 6, and so on.

2. Write a query to display even number rows first and odd number rows second

Answer:

Create a sample table:

CREATE TABLE emp_a AS
SELECT *
FROM emp
WHERE empno IN (7566, 7654, 7698, 7499, 7521, 7839);

Query:

SELECT *
FROM emp_a
WHERE MOD(empno, 2) = 0
UNION ALL
SELECT *
FROM emp_a
WHERE MOD(empno, 2) = 1;

This query displays the even empno rows first and the odd empno rows second.

3. Write a query to display even number rows

Answer:

SELECT *
FROM (
    SELECT empno, ename, ROWNUM rn
    FROM emp
)
WHERE MOD(rn, 2) = 0;

4. Write a query to display odd number rows

Answer:

SELECT *
FROM (
    SELECT empno, ename, ROWNUM rn
    FROM emp
)
WHERE MOD(rn, 2) = 1;

Quick Note

ROWNUM is assigned before sorting unless you use an inline view. If you need a specific order before applying alternate-row logic, always sort inside the subquery first.

Key Point:
Use MOD(row_number, 2) to separate even and odd rows. Use UNION ALL when you want even rows first and odd rows second.