Group by in MySQL issue

Question

I have this table

ignicao   dh_evento
1         2014-09-03 15:08:12
1         2014-09-03 15:08:26
1         2014-09-03 15:08:36
1         2014-09-03 15:08:47
0         2014-09-03 15:09:05
0         2014-09-03 15:39:05
0         2014-09-03 16:09:05
0         2014-09-03 16:39:05
0         2014-09-03 17:09:05
1         2014-09-03 17:09:13
1         2014-09-03 17:09:16
1         2014-09-03 17:09:48
1         2014-09-03 17:09:51

I want to get this result:

ignicao   dh_evento
1         2014-09-03 15:08:12
0         2014-09-03 15:09:05
1         2014-09-03 17:09:13

I'm executing this query, but i don't get this result above. this is my query:

select ignicao, dh_evento from tb_rastreamento 
where id_veiculo = 4
and dh_evento between '2014-09-03 00:00:00' and '2014-09-03 23:59:59'
group by ignicao
order by dh_evento

And i get only this result:

Ignition  dt_event
1         2014-09-03 15:08:12
0         2014-09-03 15:09:05

How can i get the result with more than 2 lines?

I'm not so good in SQL, and i'm stucked.

Answer 1

This works; only problem is that the seconds are not calculated; they always show 00. NOTE i did add a unique ID column for this to work

CREATE TABLE #TEST
(
    ID  INT,
    A   INT,
    B   SMALLDATETIME,
)

INSERT INTO #TEST VALUES(1,1,'2014-09-03 15:08:12')
INSERT INTO #TEST VALUES(2,1,'2014-09-03 15:08:26')
INSERT INTO #TEST VALUES(3,1,'2014-09-03 15:08:36')
INSERT INTO #TEST VALUES(4,1,'2014-09-03 15:08:47')
INSERT INTO #TEST VALUES(5,0,'2014-09-03 15:09:05')
INSERT INTO #TEST VALUES(6,0,'2014-09-03 15:39:05')
INSERT INTO #TEST VALUES(7,0,'2014-09-03 16:09:05')
INSERT INTO #TEST VALUES(8,0,'2014-09-03 16:39:05')
INSERT INTO #TEST VALUES(9,0,'2014-09-03 17:09:05')
INSERT INTO #TEST VALUES(10,1,'2014-09-03 17:09:13')
INSERT INTO #TEST VALUES(11,1,'2014-09-03 17:09:16')
INSERT INTO #TEST VALUES(12,1,'2014-09-03 17:09:48')
INSERT INTO #TEST VALUES(13,1,'2014-09-03 17:09:51')


CREATE TABLE #Test2
(
    ID    INT,
    A     INT,
    B     SMALLDATETIME,
    PrevA INT
)
INSERT INTO #Test2
(
    ID,
    A,
    B,
    PrevA
)
select 
    ID,
    A,
    B,
    LAG(A)  OVER (ORDER BY ID) AS 'PrevA'
from #TEST
order by B asc

select 
    A,
    B
from #Test2
Where A != PrevA OR PrevA IS NULL

Answer 2

or this (slower) solution... I left out a tiny bit for clarity...

 SELECT a.*
      , b.ignicao
   FROM 
      ( SELECT x.* 
             , COUNT(*) rank 
          FROM my_table x 
          JOIN my_table y 
            ON y.dh_evento <= x.dh_evento 
         GROUP 
            BY x.dh_evento
      ) a
   LEFT
   JOIN 
      ( SELECT x.* 
             , COUNT(*) rank 
          FROM my_table x 
          JOIN my_table y 
            ON y.dh_evento <= x.dh_evento 
         GROUP 
            BY x.dh_evento
      ) b
     ON b.ignicao = a.ignicao
    AND b.rank = a.rank - 1

  +---------+---------------------+------+---------+
  | ignicao | dh_evento           | rank | ignicao |
  +---------+---------------------+------+---------+
  |       1 | 2014-09-03 15:08:12 |    1 |    NULL |
  |       1 | 2014-09-03 15:08:26 |    2 |       1 |
  |       1 | 2014-09-03 15:08:36 |    3 |       1 |
  |       1 | 2014-09-03 15:08:47 |    4 |       1 |
  |       0 | 2014-09-03 15:09:05 |    5 |    NULL |
  |       0 | 2014-09-03 15:39:05 |    6 |       0 |
  |       0 | 2014-09-03 16:09:05 |    7 |       0 |
  |       0 | 2014-09-03 16:39:05 |    8 |       0 |
  |       0 | 2014-09-03 17:09:05 |    9 |       0 |
  |       1 | 2014-09-03 17:09:13 |   10 |    NULL |
  |       1 | 2014-09-03 17:09:16 |   11 |       1 |
  |       1 | 2014-09-03 17:09:48 |   12 |       1 |
  |       1 | 2014-09-03 17:09:51 |   13 |       1 |
  +---------+---------------------+------+---------+

Answer 3

You cannot do this with a simple group by. One method uses variables:

select r.ignicao, min(dh_evento)
from (select r.ignicao, r.dh_evento,
             (@grp := if(@i = @ignicao, @grp,
                         if(@i := @ignicao, @grp + 1, @grp + 1)
                        )
             ) as grp
      from tb_rastreamento r cross join
           (select @grp := NULL, @i := 0) vars
      where id_veiculo = 4 and dh_evento between '2014-09-03 00:00:00' and '2014-09-03 23:59:59'
      order by r.ignicao, r.dh_evento
     ) r
group by grp, r.ignicao;

Answer 4

select ignicao, min(dh_evento)
from (
    select ignicao, dh_evento,
    if(@prevIgnicao = ignicao, @groupNum, @groupNum := @groupNum + 1) groupNum,
    (@prevIgnicao := ignicao)
    from tb_rastreamento
    where id_veiculo = 4
    and dh_evento between '2014-09-03 00:00:00' and '2014-09-03 23:59:59'
    cross join (select @prevIgnicao := null, @groupNum := 0) t1
    order by dh_evento
) t1 group by ignicao, groupNum

Answer 5

Keep in mind that SQL works on data by value, not by position. Rows are not supposed to have any implicit order. So the GROUP BY groups together all the rows with the value ignicao=1, even though they are not stored physically together in the table.

If you want to treat the first batch of 1's as a separate group, you need to introduce a new enumeration to distinguish them. You could add a column to your table and store a new value, or else do it dynamically in a query like this:

SELECT ignicao, dh_evento, IF(@i=ignicao, @grp, @grp:=@grp+1) AS grp, @i:=ignicao
FROM (SELECT @i:=null, @grp:=0) AS _init, this_table
WHERE dh_evento BETWEEN '2014-09-03 00:00:00' and '2014-09-03 23:59:59'
ORDER BY dh_evento;

This increments @grp only when ignicao is not equal to the value saved from the previous row.

Then you can use that new column grp in a GROUP BY

SELECT grp, MAX(ignicao) AS ignicao, MAX(dh_evento) AS dh_evento
FROM (
    SELECT ignicao, dh_evento, IF(@i=ignicao, @grp, @grp:=@grp+1) AS grp, @i:=ignicao
    FROM (SELECT @i:=null, @grp:=0) AS _init, this_table
    WHERE dh_evento BETWEEN '2014-09-03 00:00:00' and '2014-09-03 23:59:59'
    ORDER BY dh_evento) AS t
GROUP BY grp;