Re: cheapest way to access every 15th row in table ordered by timestamp column
- From: Slim Dave <slimdave@xxxxxxxxx>
- To: Chris.Stephens@xxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Feb 2011 06:27:30 -0800 (PST)
Approximately every 15th row where each row is about two seconds apart ...
SELECT ...
from ...
where extract(second from tstamp) in (0,1,30,31)
/
?
>
>From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
>To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
>Sent: Wed, 19 January, 2011 16:20:23
>Subject: cheapest way to access every 15th row in table ordered by timestamp
>column
>
>
>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.
>
>
>
Other related posts:
