SQL - Query Help: Finding a Local Maximum

Question

I have a table which has data from a graph.

for example

index  value
0      3
1      5
2      7
3      6
4      8
5      9
6      12
7      11
8      10
9      14
10     13

I need to a query that returns the results where the value is at a local maximum, i.e. the value at a particular index is greater than the value at index+1 and index-1.

So for the example set, it should return the list of indexes: 2, 6, 9 corresponding to values 7, 12, 14.

I'm using PHP with SQLite. I can do it with a foreach-loop in php, but was wondering if there's an easy way to do it using just SQL commands.

Any input would be appreciated.

Answer 1

Doing this with a single loop in PHP is likely to be much faster than shoehorning this into an SQL query, but if you really want to you could self-join the table with itself with something like:

SELECT b.index
FROM   points AS a, points AS b, points AS c
WHERE  a.index = b.index-1 AND c.index = b.index+1
   AND a.value < b.value   AND c.value < b.value

(Untested, so *cross fingers*.)

Answer 2

You could write your loop inside a stored procedure in SQL if your SQL database supports stored procedures. However, I don't think sqlite has rich enough stored procedures to do something like this, you would need to use mysql, postgresql or similar.

Answer 3

Or use sub-queries (this is tested):

select ind
from tmp1 t1
where val > (select val from jdoyle.tmp1 t2 where t2.ind = t1.ind-1)
and val > (select val from jdoyle.tmp1 t2 where t2.ind = t1.ind+1);

Answer 4

Well, if you're using sqlite on production I imagine that you don't have a huge bunch of data. Considering this, the best solution really is to solve it at php level.