These are the tables I am having:

CREATE TABLE labels(user_id INT, session_id INT, start_time TIMESTAMP, mode TEXT);
INSERT INTO labels (user_id, session_id, start_time, mode)
VALUES (48, 652, '2016-04-01 00:47:00', 'foot'),
       (46, 663, '2016-04-01 00:13:00', 'car'),
        (9, 658, '2016-04-01 00:45:19', '');

CREATE TABLE session(session_id INT, timestamp TIMESTAMP, speed DOUBLE PRECISION);
INSERT INTO session(session_id, timestamp, speed)
VALUES (652, '2016-04-01 00:47:00', 0.77), (652, '2016-04-01 00:53:00', 3.57), (652, '2016-04-01 01:04:00', 1.25),
       (663, '2016-04-01 00:13:00', 2.5), (663, '2016-04-01 00:20:00', 12.5),
        (658, '2016-04-01 00:45:19', 2.5), (658, '2016-04-01 00:50:19', 5.3), (658, '2016-04-01 01:15:19', 7.6);

I know that the following query will return the join of these tables on session_id where mode isn't empty:

SELECT l.user_id, l.session_id, l.start_time, l.mode,  s.timestamp
FROM labels l
JOIN session s
 ON l.session_id = s.session_id
WHERE l.mode = '' IS NOT TRUE

How ever, I want to return only the timestamp of each session_id where mode isn't empty.

Expected output:

user_id session_id   start_time         mode         end_time
 48        652      2016-04-01 00:47:00 foot    2016-04-01 01:04:00
 46        663      2016-04-01 00:13:00  car    2016-04-01 00:20:00

CodePudding user response：

In your case a GROUP BY all columns from lables and the max tome for every session is one choice

SELECT DISTINCT  l.user_id, l.session_id, l.start_time, l.mode,  MAX(s.timestamp) as end_time
FROM labels l
JOIN session s
 ON l.session_id = s.session_id

WHERE l.mode = '' IS NOT TRUE
  GROUP BY l.user_id, l.session_id,l.start_time, l.mode

SELECT 2

fiddle