0

I have a database and I am facing issue to getting the data in the format below.

Table is as below

+----+---------+----------+
| ID |   ign   |   time   |
+----+---------+----------+
|  1 |    NULL | 12:30    |
|  2 |    1    | 12:31    |
|  3 |    NULL | 12:32    |
|  4 |    NULL | 12:33    |
|  5 |    NULL | 12:34    |
|  6 |    0    | 12:35    |
|  3 |    NULL | 12:36    |
|  3 |    NULL | 12:37    |
|  2 |    1    | 12:38    |
|  3 |    NULL | 12:39    |
|  4 |    NULL | 12:40    |
|  5 |    NULL | 12:41    |
|  6 |    0    | 12:42    |
|  3 |    NULL | 12:43    |
|  3 |    NULL | 12:44    |
+----+---------+----------+

My Resultant table should be in the below format.

Start  | Start Time | Stop |Stop Time
-------+------------+------+-------------
 1     |  12:31     |   0  |     12:35
 1     |  12:38     |   0  |     12:42

I am completely new to MySQL and have no idea how this can be achieved.

2
  • Specify MySQL version.
    – Akina
    Jan 27, 2020 at 11:08
  • mysql version 5.7.28-0
    – nXn
    Jan 27, 2020 at 11:15

1 Answer 1

1
SELECT 1 Start, t1.time `Start Time`, 0 Stop, MIN(t2.time) `Stop Time`
FROM src_table t1
JOIN src_table t2 ON t2.time > t1.time AND t2.ign = 0
GROUP BY t1.time
WHERE t1.ign = 1
2
  • Thank you. It would be great if you could explain the query. I am quite new to MySQL.
    – nXn
    Jan 27, 2020 at 11:19
  • 1
    @nXn Sorry, but SO is not aimed at initial training. If you don't understand a particular point-ask, but to explain everything, and even in detail, here just no one will.
    – Akina
    Jan 27, 2020 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.