How do I group segments of the data by emp_id, timein, timeout, and duration so that I can pick the one with the highest duration among the group?

CREATE TABLE visits (
visit_id INT,
emp_id INT,
time_in DATETIME,
timeout DATETIME,
duration INT
);

INSERT INTO visits (visit_id, emp_id, time_in, timeout, duration)
VALUES
(15, 2, '2012-03-14 09:30:00', '2012-03-14 10:30:00', 60),
(16, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(18, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(25, 2, '2012-03-14 10:00:00', '2012-03-14 11:00:00', 60),
(30, 5, '2012-05-16 13:00:00', '2012-05-16 14:30:00', 90),
(33, 5, '2012-05-16 13:30:00', '2012-05-16 15:30:00', 120);



---



Query #1
select
emp_id,
time_in,
timeout,
duration,
visit_id,
rank () over (partition by emp_id,time_in order by timeout desc, visit_id asc) rank

from visits;







emp_id

time_in

timeout

duration

visit_id

rank







2

2012-03-14 09:30:00

2012-03-14 10:30:00

60

15

1





2

2012-03-14 10:00:00

2012-03-14 11:00:00

60

16

1





2

2012-03-14 10:00:00

2012-03-14 11:00:00

60

18

2





2

2012-03-14 10:00:00

2012-03-14 11:00:00

60

25

3





5

2012-05-16 13:00:00

2012-05-16 14:30:00

90

30

1





5

2012-05-16 13:30:00

2012-05-16 15:30:00

120

33

1








---



View on DB Fiddle


Ideally, I want to group all emp_id = 2 visits together and extract highest duration which we see is 60 but the first row has been separated from the others under emp_id = 2. Same for emp_id = 5, which would be 120 for duration but it's saying 90 and 120. Is this where 'gap and island' come into play or is there an alternative?


Thanks