Finding the last consecutive line

Question

I need help with a request.

I have this simple table:

CREATE TABLE `consecutiv` ( `id` int(11) NOT NULL auto_increment, `readVal` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1; insert into `consecutiv`(`id`,`readVal`) values (1,2),(2,2),(3,2),(5,3),(6,3),(7,3),(8,3),(9,4),(10,5),(11,6),(12,6),(13,2),(14,2),(15,6); 

It looks like:

 id readVal 1 2 2 2 3 2 5 3 6 3 7 3 8 3 9 4 10 5 11 6 12 6 13 2 14 2 15 6 

I want to get the last consecutive line for this readVal:

In the above example:

id: 3 for readVal = 2

id: 8 for readVal = 3

 ... 

I tried with this query:

 SELECT consecutiv.id, consecutiv.readVal, c.id, c.readVal FROM consecutiv JOIN consecutiv c ON consecutiv.id = c.id-1 WHERE consecutiv.readVal!=c.readVal ORDER BY consecutiv.id ASC 

And it works as long as there is no missing identifier in the series. In the above example, id 4 is missing and the request will not return the expected result.

Thanks!

Answer 1

 SELECT a.id, a.readVal FROM consecutiv a WHERE a.readVal != (SELECT b.readVal FROM consecutiv b WHERE b.id > a.id ORDER BY id ASC LIMIT 1) ORDER BY a.id; 

returns:

  id | readval ----+--------- 3 | 2 8 | 3 9 | 4 10 | 5 12 | 6 14 | 2 

for the case when the last line is needed:

 SELECT c.id, c.readVal, c.nextReadVal FROM (SELECT a.id, a.readVal, (SELECT b.readVal FROM consecutiv b WHERE b.id > a.id ORDER BY id ASC LIMIT 1) AS nextReadVal FROM consecutiv a) AS c WHERE readVal != nextReadVal OR nextReadVal IS NULL ORDER BY c.id; 

returns:

  id | readval | nextreadval ----+---------+------------- 3 | 2 | 3 8 | 3 | 4 9 | 4 | 5 10 | 5 | 6 12 | 6 | 2 14 | 2 | 6 15 | 6 |