CODEWITHSUNDEEP
sql
HSKO
HSKO

Reputation: 11

Select an interval of values that is equal or greater than the previous value

My problem is that I don't get the first value in the interval.

The values in the increasing interval in table 1 is Id1-Id3, Id4-Id5, Id9-Id13 and Id14-Id15, but the result i Table 2 is Id2-Id3, Id5,Id10-Id13 and Id 15 missing Id1, Id4, Id9 and Id14.

I just can't figure out how to get the first value included in the query.

PSS Table containing the datasource:

ID  Date    Value

1   2012-04-20 0,166666666666667
2   2012-04-25 0,2
3   2012-04-28 0,235294117647059
4   2012-05-05 0,111111111111111
5   2012-05-07 0,416666666666667
6   2012-05-08 0,25
7   2012-05-09 0,166666666666667
8   2012-05-10 0,142857142857143
9   2012-05-11 0,125
10  2012-05-12 0,375
11  2012-05-13 0,5
12  2012-05-14 0,625
13  2012-05-15 0,75
14  2012-05-16 0,625
15  2012-05-17 0,75

And this query:

SELECT Id, Date, Value
FROM PSS p
WHERE p.Value >=
(SELECT Value
FROM PSS
WHERE Id = p.Id-1)

Resulting in tabel 2:

Id  Date    Value

2   2012-04-25 0,2
3   2012-04-28 0,235294117647059
5   2012-05-07 0,416666666666667
10  2012-05-12 0,375
11  2012-05-13 0,5
12  2012-05-14 0,625
13  2012-05-15 0,75
15  2012-05-17 0,75

Upvotes: 1

Views: 1810

Answers (1)

anouar.bagari
anouar.bagari

Reputation: 2104

Try this 

select id, date, value 
from PSS p1
where value >= (select p2.amount from PSS p2 where p2.id=p1.id - 1) 
      or value <= (select p3.amount from PSS p3 where P3.id=p1.id + 1)

Upvotes: 1

Related Questions