CODEWITHSUNDEEP
mysqlsqlstored-procedures
lintu
lintu

Reputation: 1102

MySQL select first and last records from a series of same value

I have a table like this

Id    Status        TimeStamp
1        1     2012-02-03 00:00:05
2        1     2012-02-03 00:00:10
3        0     2012-02-03 00:00:15
4        0     2012-02-03 00:00:20
5        0     2012-02-03 00:00:25
6        1     2012-02-03 00:00:30
7        1     2012-02-03 00:00:35
8        1     2012-02-03 00:00:40
9        0     2012-02-03 00:00:45
10       1     2012-02-03 00:00:50

I am using MySQL. What i need is a sql query or stored procedure which selects only rows with Ids (1,2,3,5,6,8,9,10). ie i need the first and last record from a series of same "status" records after ordering by timestamp. Is this actually doable?. Help!!!

Upvotes: 1

Views: 828

Answers (1)

Strawberry
Strawberry

Reputation: 33945

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,status        TINYINT NOT NULL
,TimeStamp TIMESTAMP NOT NULL
);

INSERT INTO my_table VALUES
(1        ,1     ,'2012-02-03 00:00:05'),
(2        ,1     ,'2012-02-03 00:00:10'),
(3        ,0     ,'2012-02-03 00:00:15'),
(4        ,0     ,'2012-02-03 00:00:20'),
(5        ,0     ,'2012-02-03 00:00:25'),
(6        ,1     ,'2012-02-03 00:00:30'),
(7        ,1     ,'2012-02-03 00:00:35'),
(8        ,1     ,'2012-02-03 00:00:40'),
(9        ,0     ,'2012-02-03 00:00:45'),
(10       ,1     ,'2012-02-03 00:00:50');

SELECT a.id start
     , MIN(c.id) End 
  FROM my_table a
  LEFT 
  JOIN my_table b 
    ON b.status = a.status
   AND b.id = a.id - 1
  LEFT 
  JOIN my_table c 
    ON c.status = a.status
   AND c.id >= a.id  
  LEFT 
  JOIN my_table d 
    ON d.status = a.status
   AND d.id = c.id + 1
 WHERE b.id IS NULL 
   AND c.id IS NOT NULL
   AND d.id IS NULL
 GROUP 
    BY a.id; 

+-------+------+
| start | End  |
+-------+------+
|     1 |    2 |
|     3 |    5 |
|     6 |    8 |
|     9 |    9 |
|    10 |   10 |
+-------+------+

Here's a fiddle for same

Upvotes: 1

Related Questions