Home » SQL & PL/SQL » SQL & PL/SQL » where in analytic function
where in analytic function [message #299842] Wed, 13 February 2008 06:21 Go to next message
niscsi
Messages: 4
Registered: December 2007
Junior Member

Hi

I can use analytic function in this way

select quantity,max(quantity) over
(
order by ttime
range between intrval '1' minute preceding
and interval '1' second preceding
) as mq
from table

but i need one more important condition on the sliding window, that the max is over just when the quantity < current_row quantity

of course this doesn't work:
'
select quantity,max(quantity) over
(
order by ttime
range between intrval '1' minute preceding
and interval '1' second preceding
where quantity< current row quantity
) as mq
from table
'
and i don't know how i can solve it just using analytic functions and case etc..
Thanks

Istvan
Re: where in analytic function [message #299851 is a reply to message #299842] Wed, 13 February 2008 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As already said to you in your previous topic:
Michel Cadot wrote on Tue, 18 December 2007 18:15
In addition,
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Don't put only one word per line.
Always post your Oracle version (4 decimals).

So start with this and indent the query.

Regards
Michl

[Updated on: Wed, 13 February 2008 06:41]

Report message to a moderator

Re: where in analytic function [message #299857 is a reply to message #299842] Wed, 13 February 2008 07:05 Go to previous message
niscsi
Messages: 4
Registered: December 2007
Junior Member

Hi

I can use analytic function in this way

select quantity,max(quantity) over
(
order by ttime
range between intrval '1' minute preceding
and interval '1' second preceding
) as mq
from table


but i need one more important condition on the sliding window, that the max is over just when the quantity < current_row quantity.

of course this doesn't work:

select quantity,max(quantity) over
(
order by ttime
range between intrval '1' minute preceding
and interval '1' second preceding
where quantity< current row quantity
) as mq
from table


and i don't know how i can solve it just using analytic functions and case etc..Thanks

Istvan


Oracle version 10.2.0.1
Previous Topic: Oracle 9 i
Next Topic: Create Calendar w/ sql
Goto Forum:
  


Current Time: Wed Dec 07 12:43:20 CST 2016

Total time taken to generate the page: 0.16100 seconds