Selecting Every Other Row

One of the fun things about publishing is getting questions from readers that make you think. A recent question I received went something like this: “Can I get the odd and even number of rows from a DB2 table?”

Well, my first reaction was to think “this guy doesn’t understand the way a SQL DBMS like DB2 works.” The data in DB2 tables is not ordered, so there is no way to guarantee that the rows are odd or even numbered. While my observation on the questioner’s skills may (or may not) have been true, it didn’t help the guy. So I thought about it and came up with a possible solution.

The first thing we have to do is to mimic row numbers in DB2. Until V9, DB2 did not support the row number construct (such as you can find in Oracle), so I first decided to come up with a solution that would work even for very old versions of DB2.

So, to do this we start by using 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 results that we can access in our SQL predicates. If, say, we only want to return the even results, we could write the following query:

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;

The MOD function returns the remainder of dividing the second argument into the first. So, if the remainder is zero, we have an even number. So, this query returns every other row to the result set. If you want the odd rows only, change the predicate with the MOD function to this:

WHERE MOD(ROWNUM,2) <> 0

Of course, there is no guarantee that the same exact rows will be even (or odd) for subsequent executions of this query. It all depends how DB2 optimizes the query for execution. But it does provide a nice way to produce samples of the data (perhaps to populate a test bed of data).

Even better, for anyone using a supported version of DB2 (that would be 10 or 11 at this point in time), you can use the ROW_NUMBER function.

SELECT DEPTNO,

       ROW_NUMBER() OVER(ORDER BY DEPTNO) AS ROWNUM,

FROM DSN8810.DEPT;

The ORDER BY is optional and if it is not specified the results will not be returned in any particular order. That is, the row numbers will be assigned to the rows arbitrarily. As with the other queries, you can add the MOD clause to select even/odd rows (but keep in mind that even and odd rows are not really a relational concept).

This blog was originally published on Craig Mullins’ blog at: https://db2portal.blogspot.com/2008/07/selecting-every-other-row.html