'Mysql select copy column to another, id no successive
i want to get the interval of time of every event in all the areas of my work place, so i got this table that shows the time an event ocurr
ID | area | TIME 1
01 | A | 2016-09-20 14:55:54
02 | A | 2016-09-21 18:00:00
04 | A | 2016-09-23 20:14:14
06 | B | 2016-09-24 13:52:24
10 | B | 2016-09-26 13:33:34
13 | B | 2016-09-28 18:22:46
as we can see, the id is not successive because there's another data filtered in the query, i want the output to be like this:
area | TIME 1 | TIME 2
A | 2016-09-20 14:55:54 | 2016-09-21 18:00:00
A | 2016-09-21 18:00:00 | 2016-09-23 20:14:14
B | 2016-09-24 13:52:24 | 2016-09-26 13:33:34
B | 2016-09-26 13:33:34 | 2016-09-28 18:22:46
is there a way to do it in the select?, i use a inner join to the same "time 1", but it gets the "time 2" for every "time 1" resulting in, for area A:
area | TIME 1 | TIME 2
A | 2016-09-20 14:55:54 | 2016-09-20 14:55:54
A | 2016-09-20 14:55:54 | 2016-09-21 18:00:00
A | 2016-09-20 14:55:54 | 2016-09-23 20:14:14
A | 2016-09-21 18:00:00 | 2016-09-20 14:55:54
A | 2016-09-21 18:00:00 | 2016-09-21 18:00:00
A | 2016-09-21 18:00:00 | 2016-09-23 20:14:14
A | 2016-09-23 20:14:14 | 2016-09-20 14:55:54
A | 2016-09-23 20:14:14 | 2016-09-21 18:00:00
A | 2016-09-23 20:14:14 | 2016-09-23 20:14:14
Solution 1:[1]
SELECT *
FROM Table1 T1
JOIN Table1 T2
ON T1.area = T2.area
AND T2.ID = (SELECT MIN(T3.ID)
FROM Table1 T3
WHERE T3.ID > T1.ID
AND T3.area = T1.area)
OUTPUT
EDIT : I assume if ID_1 < ID_2
then Time_1 < Time_2
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Glorfindel |