Find next row using on condition in MySQL

114
September 08, 2018, at 9:50 PM

I have the following table:

Here is the full table: http://sqlfiddle.com/#!9/59e05b/5

I need the records to be sorted by event_time and for a specific visitor and then find the time difference between the fields.

I wrote the following query:

    SELECT 
A.id, A.visitor_id, A.field_label, A.field_number, A.form_step_num, A.field_type, A.event_time, B.event_time, B.event_time - A.event_time as timediff
FROM form_events A 
INNER JOIN form_events B 
ON B.id = (A.id + 1) 
and A.visitor_id = B.visitor_id 
and A.form_unique_identifier = B.form_unique_identifier ORDER BY A.event_time ASC

But the issue is it gives wrong results. How to get the next event time without using B.id = (A.id + 1)?

Here is the right output which I require:

id  visitor_id  field_label                 field_number    form_step_num   field_type  event_time          event_time              timediff
1   455         Your Name (required)        5               1               input       2018-08-28T08:14:00Z    2018-08-28T08:14:03Z    3
2   455         Your Email (required)       6               1               input       2018-08-28T08:14:03Z    2018-08-28T08:14:03Z    0
4   455         Your Name (required)        5               1               input       2018-08-28T08:14:03Z    2018-08-28T08:14:06Z    3
3   455         Your Email (required)       6               1               input       2018-08-28T08:14:06Z    2018-08-28T08:14:10Z    4
5   455         Subject                     7               1               input       2018-08-28T08:14:10Z    2018-08-28T08:14:10Z    0
6   455         Your Email (required)       6               1               input       2018-08-28T08:14:10Z    2018-08-28T08:14:13Z    3
7   455         Subject                     7               1               input       2018-08-28T08:14:13Z    2018-08-28T08:14:13Z    0
9   455         Your Message                8               1               textarea    2018-08-28T08:14:13Z    2018-08-28T08:14:13Z    0
10  455         Your Message                8               1               textarea    2018-08-28T08:14:13Z    2018-08-28T08:14:15Z    2
8   455         form                        0               1               form        2018-08-28T08:14:15Z                            0
13  458         Your Name (required)        5               0               input       2018-08-28T09:34:33Z    2018-08-28T09:34:38Z    5
14  458         Your Name (required)        5               0               input       2018-08-28T09:34:38Z    2018-08-28T09:34:40Z    2
15  458         Your Email (required)       6               0               input       2018-08-28T09:34:40Z    2018-08-28T09:34:52Z    12
16  458         Subject                     7               0               input       2018-08-28T09:34:52Z    2018-08-28T09:34:52Z    0
17  458         Your Email (required)       6               0               input       2018-08-28T09:34:52Z    2018-08-28T09:34:57Z    5
18  458         Your Message                8               0               textarea    2018-08-28T09:34:57Z    2018-08-28T09:34:57Z    0
19  458         Subject                     7               0               input       2018-08-28T09:34:57Z                            0
Answer 1

You are looking for LEAD function, but it didn't support on MySQL 5.6

So You can try to write a subquery to make it.

Schema (MySQL v8.0)

CREATE TABLE `form_events` (
  `id` int(10) UNSIGNED NOT NULL,
  `visitor_id` int(10) UNSIGNED NOT NULL,
  `page_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `form_unique_identifier` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `form_step_num` int(10) UNSIGNED NOT NULL,
  `field_number` int(10) UNSIGNED NOT NULL,
  `form_method` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `form_action` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `form_css_id` text COLLATE utf8mb4_unicode_ci,
  `form_css_class` text COLLATE utf8mb4_unicode_ci,
  `field_label` text COLLATE utf8mb4_unicode_ci,
  `field_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `field_css_id` text COLLATE utf8mb4_unicode_ci,
  `field_name` text COLLATE utf8mb4_unicode_ci,
  `field_css_class` text COLLATE utf8mb4_unicode_ci,
  `event_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `form_events`
--
INSERT INTO `form_events` (`id`, `visitor_id`, `page_url`, `form_unique_identifier`, `form_step_num`, `field_number`, `form_method`, `form_action`, `form_css_id`, `form_css_class`, `field_label`, `field_type`, `field_css_id`, `field_name`, `field_css_class`, `event_name`, `event_time`, `created_at`, `updated_at`) VALUES
(1, 455, 'http://test.com/', 'FA67841442', 1, 5, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Name (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-validates-as-required', 'click', '2018-08-28 08:14:00', '2018-08-28 08:14:03', '2018-08-28 08:14:03'),
(2, 455, 'http://test.com/', 'FA67841442', 1, 6, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Email (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email', 'select', '2018-08-28 08:14:03', '2018-08-28 08:14:06', '2018-08-28 08:14:06'),
(3, 455, 'http://test.com/', 'FA67841442', 1, 6, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Email (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email', 'select', '2018-08-28 08:14:06', '2018-08-28 08:14:06', '2018-08-28 08:14:06'),
(4, 455, 'http://test.com/', 'FA67841442', 1, 5, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Name (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-validates-as-required', 'change', '2018-08-28 08:14:03', '2018-08-28 08:14:07', '2018-08-28 08:14:07'),
(5, 455, 'http://test.com/', 'FA67841442', 1, 7, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Subject\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text', 'click', '2018-08-28 08:14:10', '2018-08-28 08:14:13', '2018-08-28 08:14:13'),
(6, 455, 'http://test.com/', 'FA67841442', 1, 6, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Email (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email', 'change', '2018-08-28 08:14:10', '2018-08-28 08:14:13', '2018-08-28 08:14:13'),
(7, 455, 'http://test.com/', 'FA67841442', 1, 7, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Subject\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text', 'change', '2018-08-28 08:14:13', '2018-08-28 08:14:17', '2018-08-28 08:14:17'),
(8, 455, 'http://test.com/', 'FA67841442', 1, 0, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', 'form', 'form', '', NULL, 'wpcf7-form', 'submit', '2018-08-28 08:14:15', '2018-08-28 08:14:17', '2018-08-28 08:14:17'),
(9, 455, 'http://test.com/', 'FA67841442', 1, 8, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Message\n      ', 'textarea', '', NULL, 'wpcf7-form-control wpcf7-textarea', 'select', '2018-08-28 08:14:13', '2018-08-28 08:14:17', '2018-08-28 08:14:17'),
(10, 455, 'http://test.com/', 'FA67841442', 1, 8, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Message\n      ', 'textarea', '', NULL, 'wpcf7-form-control wpcf7-textarea', 'select', '2018-08-28 08:14:13', '2018-08-28 08:14:17', '2018-08-28 08:14:17'),
(11, 455, 'http://test.com/', 'FA67841442', 1, 8, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Message\n    scdcscd  ', 'textarea', '', NULL, 'wpcf7-form-control wpcf7-textarea', 'change', '2018-08-28 08:14:15', '2018-08-28 08:14:17', '2018-08-28 08:14:17'),
(13, 458, 'http://test.com/', 'FA67841442', 0, 5, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Name (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-validates-as-required', 'click', '2018-08-28 09:34:33', '2018-08-28 09:34:36', '2018-08-28 09:34:36'),
(14, 458, 'http://test.com/', 'FA67841442', 0, 5, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Name (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-validates-as-required', 'change', '2018-08-28 09:34:38', '2018-08-28 09:34:39', '2018-08-28 09:34:39'),
(15, 458, 'http://test.com/', 'FA67841442', 0, 6, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Email (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email', 'click', '2018-08-28 09:34:40', '2018-08-28 09:34:43', '2018-08-28 09:34:43'),
(16, 458, 'http://test.com/', 'FA67841442', 0, 7, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Subject\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text', 'click', '2018-08-28 09:34:52', '2018-08-28 09:34:55', '2018-08-28 09:34:55'),
(17, 458, 'http://test.com/', 'FA67841442', 0, 6, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Email (required)\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email', 'change', '2018-08-28 09:34:52', '2018-08-28 09:34:56', '2018-08-28 09:34:56'),
(18, 458, 'http://test.com/', 'FA67841442', 0, 8, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Message\n     ', 'textarea', '', NULL, 'wpcf7-form-control wpcf7-textarea', 'click', '2018-08-28 09:34:57', '2018-08-28 09:34:59', '2018-08-28 09:34:59'),
(19, 458, 'http://test.com/', 'FA67841442', 0, 7, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Subject\n     ', 'input', '', NULL, 'wpcf7-form-control wpcf7-text', 'change', '2018-08-28 09:34:57', '2018-08-28 09:34:59', '2018-08-28 09:34:59'),
(20, 458, 'http://test.com/', 'FA67841442', 0, 8, 'post', 'http://test.com/#wpcf7-f9-p10-o1', '', 'wpcf7-form', ' Your Message\n     ', 'textarea', '', NULL, 'wpcf7-form-control wpcf7-textarea', 'change', '2018-08-28 09:35:01', '2018-08-28 09:35:03', '2018-08-28 09:35:03');

Query #1

SELECT *, coalesce(event_timeB - A.event_time,0) as timediff
FROM (
    SELECT 
    A.id, 
    A.visitor_id, 
    A.field_label,
    A.field_number,
    A.form_step_num,
    A.field_type,
    A.event_time, 
    (
      SELECT B.event_time 
      FROM form_events B 
      WHERE 
          A.visitor_id = B.visitor_id  
      AND 
          A.form_unique_identifier = B.form_unique_identifier 
      AND
          A.event_time <= B.event_time  
      AND 
          (A.ID < B.ID OR A.event_time < B.event_time )
      ORDER BY B.event_time
      LIMIT 1
    ) event_timeB  
    FROM form_events A
)A
ORDER BY A.event_time ASC

Result

id  visitor_id  field_label                 field_number    form_step_num   field_type  event_time          event_time              timediff
1   455         Your Name (required)        5               1               input       2018-08-28T08:14:00Z    2018-08-28T08:14:03Z    3
2   455         Your Email (required)       6               1               input       2018-08-28T08:14:03Z    2018-08-28T08:14:03Z    0
4   455         Your Name (required)        5               1               input       2018-08-28T08:14:03Z    2018-08-28T08:14:06Z    3
3   455         Your Email (required)       6               1               input       2018-08-28T08:14:06Z    2018-08-28T08:14:10Z    4
5   455         Subject                     7               1               input       2018-08-28T08:14:10Z    2018-08-28T08:14:10Z    0
6   455         Your Email (required)       6               1               input       2018-08-28T08:14:10Z    2018-08-28T08:14:13Z    3
7   455         Subject                     7               1               input       2018-08-28T08:14:13Z    2018-08-28T08:14:13Z    0
9   455         Your Message                8               1               textarea    2018-08-28T08:14:13Z    2018-08-28T08:14:13Z    0
10  455         Your Message                8               1               textarea    2018-08-28T08:14:13Z    2018-08-28T08:14:15Z    2
8   455         form                        0               1               form        2018-08-28T08:14:15Z                            0
13  458         Your Name (required)        5               0               input       2018-08-28T09:34:33Z    2018-08-28T09:34:38Z    5
14  458         Your Name (required)        5               0               input       2018-08-28T09:34:38Z    2018-08-28T09:34:40Z    2
15  458         Your Email (required)       6               0               input       2018-08-28T09:34:40Z    2018-08-28T09:34:52Z    12
16  458         Subject                     7               0               input       2018-08-28T09:34:52Z    2018-08-28T09:34:52Z    0
17  458         Your Email (required)       6               0               input       2018-08-28T09:34:52Z    2018-08-28T09:34:57Z    5
18  458         Your Message                8               0               textarea    2018-08-28T09:34:57Z    2018-08-28T09:34:57Z    0
19  458         Subject                     7               0               input       2018-08-28T09:34:57Z                            0

View on DB Fiddle

Answer 2

The only issue, this will output 2 columns that you don't need.

SELECT 
A.id, A.visitor_id, A.field_label, A.field_number, A.form_step_num, A.field_type, A.event_time,
case when @visitor_id <> visitor_id then @time := event_time end,
@visitor_id := visitor_id,
event_time - @time as timediff
FROM form_events A, (select @visitor_id := 0, @time := 0) t
ORDER BY a.visitor_id, A.event_time ASC
Rent Charter Buses Company
READ ALSO
Get parent row which is followed by the child rows

Get parent row which is followed by the child rows

The other parent->child answers I've found don't really seem to address my situation so here goes

121
MySQL, identical alias and column name, which takes precedence?

MySQL, identical alias and column name, which takes precedence?

If MySQL encounters an identical column name and alias name, does one or the other always take precedence? eg

106