Re: Filtered Query on nth row

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/06
Message-ID: <34dacb79.4271215@www.sigov.si>#1/1

On Thu, 05 Feb 1998 19:29:08 -0500, Jeff Jackson <jeff_at_tatca.tc.faa.gov> wrote:

>Does anyone know how to do a query and filter every nth row ? Example,
>I have 100 rows returned but I am only intereseted in every other fith
>row for example.... row1, row6, row11,row16 etc.... I don't want to see
>rows 2,3,4,5,7,8,9,10 etc ... Is this possible ?
>
>Does SQL allow me to do this ? I have tried all kinds of queries,
>correlated, inner joins, sub queries and various expressions.

I'm not sure if "pure" SQL can do this, but Oracle's pseudocolumn ROWNUM enables you to do what you are looking for.

Let's take well known SCOTT.EMP table for demonstration. If you want to display only every third row from ordinary

  "SELECT empno, ename, sal FROM emp",

where the order of the basic select doesn't matter you can use the following:

SQL> SELECT a.empno, a.ename, a.sal FROM emp a,   2 (SELECT mod(rownum,3) x, empno FROM emp) b   3 WHERE a.empno = b.empno
  4 AND b.x = 1;

    EMPNO ENAME SAL
--------- ---------- ---------

     7369 SMITH            800
     7566 JONES           2975
     7782 CLARK           2450
     7844 TURNER          1500
     7902 FORD            3000

If your basic select must be ordered, then things get more complicated, because of the nature how ROWNUM is assigned to each returned row. Suppose you want to select rows ordered by salary, but display only each third row. To get the result, use this:

SQL> SELECT a.empno, a.ename, a.sal FROM emp a,   2 (SELECT mod(rownum,3) x, empno FROM emp, dual   3 WHERE emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL)) b   4 WHERE a.empno = b.empno
  5 AND b.x = 1;

    EMPNO ENAME SAL
--------- ---------- ---------

     7369 SMITH            800
     7521 WARD            1250
     7844 TURNER          1500
     7698 BLAKE           2850
     7902 FORD            3000

Both of this sollutions will work only with Oracle 7.2 and above, where in-line views are supported. For prior versions, view should be created explicitely.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Feb 06 1998 - 00:00:00 CST