Rida Rida - 6 months ago 8
SQL Question

How can I return the next row of WHERE clause?

I am using mysql DB server ..
I have the following table that consists of only one column with the following data (where 0's separate sorted integers)

Number
-------
0
1
2
3
0
4
5
0
6
7
8
0
9
10
0
11


I want to get the first value that comes after each 0 , so e.g. output would be

Output
------
1
4
6
9
11

Answer
SELECT id1 FROM table WHERE id IN
(SELECT t1.id+1 FROM table t1 
LEFT JOIN table t2 ON t1.id1=t2.id 
WHERE t2.id1 IS NULL);

SQL Fiddle

Assuming no gaps in the increment field,but since it will be created..

Comments