Re: cheapest way to access every 15th row in table ordered by timestamp column

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 19 Jan 2011 10:16:45 -0800
Message-ID: <AANLkTi==nScj6RJ5DA398zW4eyG6kmNs56Mhu_zkSvuu_at_mail.gmail.com>



These give different results:
SELECT *
  FROM (SELECT object_name,
               ROW_NUMBER () OVER (ORDER BY timestamp) rnum,
               timestamp
          FROM dba_objects)

 WHERE MOD (rnum, 15) = 0;

  SELECT *
    FROM ( SELECT object_name, ROWNUM rnum, timestamp

              FROM dba_objects
          ORDER BY timestamp)

   WHERE MOD (rnum, 15) = 0
ORDER BY rnum;

Different because in the 1st select the rows are sorted first, then the row_number assigned.
In the 2nd example, the row numbers are assigned and then the rows are sorted.

I think the first format is the one you want.

Mike

On Wed, Jan 19, 2011 at 8:20 AM, Stephens, Chris <Chris.Stephens_at_adm.com>wrote:

> 11.2.0.2 on Linux 5
>
>
>
> We have a lab instrument recording information every 2 seconds into a
> table.
>
>
>
> The scientists pull that data into excel for analysis (I hope to look at
> this excel spreadsheet in the very near future now that I’ve read chapter 9
> of Pro Oracle SQL).
>
>
>
> They are requesting a view that picks out every 15th row of data going back
> 30 days.
>
>
>
> Can anyone think of a more efficient way to do it than this:
>
>
>
> SELECT x.col,
>
> x.t_stamp
>
> FROM ( SELECT col,
>
> t_stamp
>
> rownum rn
>
> FROM t
>
> WHERE t_stamp >= TRUNC( SYSDATE - 30 )) x
>
> WHERE MOD( x.rn, 15 ) = 0;
>
>
>
>
>
> It feels like there should be a better way to do it.
>
>
>
> chris
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which
> it is addressed and may contain information that is privileged, confidential
> and exempt from disclosure under applicable law. If the reader of this
> message is not the intended recipient or the employee or agent responsible
> for delivering this message to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 19 2011 - 12:16:45 CST

Original text of this message