GROUP BY for continuous rows in SQL

Question

Given the following table:

  ID State Date
 12 1 2009-07-16 10:00
 45 2 2009-07-16 13:00
 67 2 2009-07-16 14:40
 77 1 2009-07-16 15:00
 89 1 2009-07-16 15:30
 99 1 2009-07-16 16:00

Question:
How can I GROUP on the "State" field while maintaining the boundaries between state changes?

SELECT MIN(ID) AS ID, State, MIN(Date) AS Date, COUNT(ID) AS Count FROM table GROUP BY State 

leads to the following:

  ID State Date Count
 12 1 2009-07-16 10:00 4
 45 2 2009-07-16 13:00 2

but it is expected:

  ID State Date Count
 12 1 2009-07-16 10:00 1
 45 2 2009-07-16 13:00 2
 77 1 2009-07-16 15:00 3

Is this possible in SQL? I have not found a solution yet ...

Answer 1

 SELECT MIN(id) AS id, MIN(ts) AS ts, MIN(state) AS state, COUNT(*) cnt FROM ( SELECT @r := @r + (@state != state) AS gn, @state := state AS sn, s.* FROM ( SELECT @r := 0, @state := 0 ) vars, t_state s ORDER BY ts ) q GROUP BY gn 

Scripts for creating a table for testing:

 CREATE TABLE t_state (id INT NOT NULL PRIMARY KEY, state INT NOT NULL, ts DATETIME NOT NULL); INSERT INTO t_state VALUES (12, 1, '2009-07-16 10:00'), (45, 2, '2009-07-16 13:00'), (67, 2, '2009-07-16 14:40'), (77, 1, '2009-07-16 15:00'), (89, 1, '2009-07-16 15:30'), (99, 1, '2009-07-16 16:00'); 

Answer 2

Here's how to do it with CTE on an MSSQL server

 -- DROP TABLE MyLog CREATE TABLE MyLog( ID INT PRIMARY KEY , State INT , Date DATETIME ) INSERT MyLog SELECT 12, 1, '2009-07-16 10:00' UNION ALL SELECT 45, 2, '2009-07-16 13:00' UNION ALL SELECT 67, 2, '2009-07-16 14:40' UNION ALL SELECT 77, 1, '2009-07-16 15:00' UNION ALL SELECT 89, 1, '2009-07-16 15:30' UNION ALL SELECT 99, 1, '2009-07-16 16:00' ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo , * FROM MyLog ) , MyLogGroup AS ( SELECT l.* , ( SELECT MAX(ID) FROM CTE c WHERE NOT EXISTS (SELECT * FROM CTE WHERE RowNo = c.RowNo-1 AND State = c.State) AND c.ID <= l.ID) AS GroupID FROM MyLog l ) SELECT * FROM MyLogGroup 

Answer 3

Here is a longer description of how solutions like those offered by Quassnoi work

Answer 4

I could state the obvious here, but if you want to use Transact-SQL, you can iterate through the rows of the table and create your own result set, which probably seems troublesome, but it will certainly work. Iteration can be performed without using cursors .