MySQL Check if consecutive columns match and only those rows are displayed

Question

I have a table eventsthat contains identifiers ( id), and dates ( eventDate) corresponding to these identifiers ( idand eventDateare not the only columns in the table).

SQLFiddle here .

+--------+----+---------------------+
| row_id | id | eventDate           |
+--------+----+---------------------+
|      1 |  1 | 2014-02-27 23:19:41 |
|      2 |  1 | 2014-02-27 23:21:41 |
|      3 |  1 | 2014-02-27 23:21:41 |
|      4 |  2 | 2014-02-27 23:23:08 |
|      5 |  2 | 2014-02-27 23:25:08 |
|      6 |  2 | 2014-02-27 23:25:08 |
|      9 |  3 | 2014-02-28 15:36:55 |
|      8 |  3 | 2014-02-28 15:36:55 |
|      7 |  3 | 2014-02-28 15:34:55 |
|     10 |  4 | 2014-02-28 19:31:31 |
|     11 |  4 | 2014-02-28 19:33:31 |
|     12 |  4 | 2014-02-28 19:33:31 |
|     13 |  5 | 2014-02-28 19:33:34 |
|     14 |  5 | 2014-02-28 19:33:33 |
|     15 |  5 | 2014-02-28 19:31:33 |
|     16 |  6 | 2014-03-04 22:40:21 |
|     17 |  6 | 2014-03-04 22:38:21 |
|     18 |  6 | 2014-03-04 22:40:21 |
|     19 |  7 | 2014-03-04 23:08:37 |
|     20 |  7 | 2014-03-04 23:08:38 |
+--------+----+---------------------+

I want to select only those rows from the table where the consecutive event dates are the same for the same ID.

So, I would like to see only these entries -

+----+---------------------+
| id | eventDate           |
+----+---------------------+
|  1 | 2014-02-27 23:21:41 |
|  1 | 2014-02-27 23:21:41 |
|  2 | 2014-02-27 23:25:08 |
|  2 | 2014-02-27 23:25:08 |
|  3 | 2014-02-28 15:36:55 |
|  3 | 2014-02-28 15:36:55 |
|  4 | 2014-02-28 19:33:31 |
|  4 | 2014-02-28 19:33:31 |

Please note that no

|  6 | 2014-03-04 22:40:21 |
|  6 | 2014-03-04 22:40:21 |

in the above result, because they are not consistent.

, SQL- , unix , , SQL.

Answer 1

, , mySql .

SELECT t.*
FROM (
    SELECT  
           id,
           eventDate,
           COUNT(0) AS numRows
    FROM tabl
    GROUP BY id, DATE(eventDate)
    HAVING COUNT(0) > 1
    ORDER BY eventDate
) t

, .

Answer 2

id, eventDate from your_tableName, eventDate in ( eventDate your_tableName id, eventDate count (eventDate) > 1);

Answer 3

select ta.id, ta.eventDate from
(
    select row_id as ra, t1.id, t1.eventDate
    from events t1

) as ta
 join
(
     select row_id as rb, t2.id, t2.eventDate
     from events t2
) as tb
on rb = ra+1 and ta.id = tb.id and ta.eventDate = tb.eventDate

Answer 4

eventDate , , - 1 . 1 .

SET @inc = 0;
SET @innerInc = 1;

SELECT t1.id, t1.eventDate
FROM (
    SELECT id, eventDate, (@inc := @inc + 1) as increment FROM temp
) t1
WHERE t1.eventDate = (
    SELECT t2.eventDate FROM (
        SELECT eventDate, (@innerInc := @innerInc + 1) as increment FROM temp
    ) t2
    WHERE t2.increment = t1.increment
);

SQLFiddle :

Answer 5

( , ..)

SELECT t.id,t.eventDate
FROM (
    SELECT
        IF(id = @prevID AND eventDate = @prevDate, @counter, @counter := @counter+1) as c,
        @prevID := id as id,
        @prevDate := eventDate as eventDate
    FROM events e
    JOIN (SELECT @counter := 0, @prevID := NULL, @prevDate := NULL) as stuff
    WHERE 1 #or some where condition for events
    ORDER BY row_id ASC
) as t
GROUP BY t.c

Answer 6

, , eventDate eventDate, , :

select * 
from Table a
join (select eventDate, count(*)
      from Table
      group by eventDate
      having count(*) > 1) b
on (a.eventDate = b.eventDate)

, , , . , , .

Answer 7

After you have selected my self join, I think you will have to generate row_numbers for each sub_query:

select @rn1 := @rn1+1 as ra, t1.id, t1.eventDate
from events t1
join (select @rn1 := 0) r;

and then attach this to

select @rn2 := @rn2+1 as rb, t2.id, t2.eventDate
from events t2
join (SELECT @rn2 := 0) r;

so the final answer is:

select ta.id, ta.eventDate from
(
    select @rn1 := @rn1+1 as ra, t1.id, t1.eventDate
    from events t1
    join (select @rn1 := 0) r
) as ta
 join
(
     select @rn2 := @rn2+1 as rb, t2.id, t2.eventDate
     from events t2
     join (SELECT @rn2 := 0) r
) as tb
on rb = ra+1 and ta.id = tb.id and ta.eventDate = tb.eventDate

Results:

February 1, 27 2014 23: 21: 41 + 0000

February 2, 27 2014 23: 25: 08 + 0000

February 3, 28 2014 15: 36: 55 + 0000

February 4, 28 2014 19: 33: 31 + 0000