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
Dig Deeper on DB2 UDB (universal databases)
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.