Re: select nth row ?

From: Rod Corderey <Lane_Associates_at_compuserve.com>
Date: Fri, 24 Apr 1998 19:34:26 +0100
Message-ID: <3540DB32.D90AE758@compuserve.com>

Phil,

the following is a little contrived but it does work. You do of course need to be very careful with use of indexes if the inner dataset is very large. However you may be able to contrive the inner evaluation purely from indexes and then attach any subsequent requirement to the outer reduced set.

I am using a table called absence types which has an attribute of absence_type.

Retrieve every nth row from the table absence_types after ordering by absence_type.

select rownum, absence_type
from ( select absence_type, rownum order_no

        from ( select absence_type from absence_types
               group by absence_type
             )
      ) a

where mod(a.order_no , &_value_of_n) = 0 and rownum <= &_number_of_sets

result where number_of_sets = 5 is

    ROWNUM ABSENCE_TYPE
---------- ------------

         1         2069
         2         4838
         3         5458
         4         5466
         5         7852

Raw data out of in-line query a =

    ROWNUM ABSENCE_TYPE
---------- ------------

         1            1
         2            2
         3            3
         4         1968
         5         2067
         6         2069
         7         2122
         8         2182
         9         2186
        10         2187
        11         2622

    ROWNUM ABSENCE_TYPE
---------- ------------

        12         4838
        13         4913
        14         5100
        15         5448
        16         5452
        17         5455
        18         5458
        19         5459
        20         5460
        21         5461
        22         5464

    ROWNUM ABSENCE_TYPE
---------- ------------

        23         5465
        24         5466
        25         5664
        26         5685
        27         5777
        28         5821
        29         7114
        30         7852
        31         8778
        32        10299
        33        10900

    ROWNUM ABSENCE_TYPE
---------- ------------

        34        10945
        35        11024
        36        11203
        37        11777
        38        11842

Hope this helps,

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Phil R Lawrence wrote:
>
> Hello,
>
> I am treating the results of an ordered query as a data set. As such, I am
> interested in determining the mean and interquartile range. This means I need
> to be able to select the nth row of the *ordered* data set. Now, I thought I
> might use ROWNUM, but my manual says "ROWNUM is assigned before the rows are
> ordered [by the ORDER BY clause]."
>
> Any ideas on how I can do this? If I can't get Oracle to do this I'll have to
> slurp the whole data set into my program and do the calcs there.
>
> Thanks!
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Phil R Lawrence phone: 610-758-3051
> Programmer / Analyst e-mail: prl2_at_lehigh.edu
> 194 Lehigh University Computing Center
> E.W. Fairchild - Martindale, Bldg. 8B
> Bethlehem, PA 18018
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Received on Fri Apr 24 1998 - 13:34:26 CDT