Select Page

Selecting Every Other Row

Craig Mullins | | March 15, 2016

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

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS