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 by the field "State", while still maintaining the borders between the state changes?

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

results in the following:

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

but this 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 didn't find a solution so far...

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

Table creation scripts 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

I might be stating the obvious here, but if you're willing to make use of Transact-SQL, you can iterate through the rows of the table and build your own result set, which probably seems like a hassle, but it will definitely work. The iteration can be done without the use of cursors.

Answer 3

This is how to do it with CTEs on 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