Re: Searching for big differences between values

Durumdara wrote:
> Somewhere the users made mistakes on prices (stock).
> 
> I need to search for big differences between values.
> For example:
> 
> 20
> 21
> 21,5
> 30
> 28
> ..
> 46392 <-
> 46392 <-
> 
> But it could be:
> 
> 42300
> 43100
> 44000
> 43800
> 65000 <-
> 42100
> 
> Human eye could locate these values, but there we need to check 30.000 articles and 450.000 values.
> 
> Do you have any idea, how to this with SQL?

You could use some variant of
 SELECT id, price FROM (SELECT id, price,              avg(price) OVER (ORDER BY id ROWS BETWEEN 3 PRECEDING AND 3
FOLLOWING)AS av       FROM stock) q WHERE NOT (price BETWEEN 0.8 * av AND 1.25 * av);
 

Yours,
Laurenz Albe