Re: cheapest way to access every 15th row in table ordered by timestamp column
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Chris.Stephens@xxxxxxx
- Date: Wed, 19 Jan 2011 17:34:39 +0100
Chris,
There is no guarantee that the rows will be returned in order if it
matters. Instead of rownum you should use
row_number() over(order by t_stamp) rn.
Otherwise the query looks reasonable to me.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 01/19/2011 05:20 PM, Stephens, Chris 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.
>
>
Other related posts:
