Ask the Expert

How can I get the odd and even number of rows from a DB2 table?

Can I get the odd and even number of rows from a DB2 table? If this is possible, do you have access to the query with examples? Thank you.
Well, first of all, let me correct what is perhaps a misunderstanding about DB2 tables. The data in the rows is not ordered. So there is no way to guarantee that the rows are odd or even numbered.

With that aside, let me try to come up with a "solution" to your problem. The first thing you have to do is to mimic row numbers in DB2. DB2 does not support the row number construct (such as you can find in Oracle). To do this, use the COUNT(*) function and a table expression. A table expression is when you substitute SQL in place of the table in the FROM clause of another SQL statement. For example, consider this SQL: 

SELECT  DEPTNO, ROWNUM
FROM DSN8810.DEPT A,
TABLE (SELECT COUNT(*) + 1 AS ROWNUM
FROM DSN8810.DEPT B
WHERE B.DEPTNO < A.DEPTNO) AS TEMP_TAB;
That puts a pseudo-row number on the table. Now, say you only want the even results. This can be done using DB2 functions. 
SELECT  DEPTNO, ROWNUM
FROM DSN8810.DEPT A,
TABLE (SELECT COUNT(*) + 1 AS ROWNUM
FROM DSN8810.DEPT B
WHERE B.DEPTNO < A.DEPTNO) AS TEMP_TAB
WHERE MOD(ROWNUM,2) = 0
ORDER BY ROWNUM;
If you want the odd rows only, change the predicate with the MOD function to this: 
WHERE MOD(ROWNUM,2) <> 0

This was first published in February 2005

Back to top