I have a pre-processed table which I want to group every pair into one record containing data from fields of both records.
|-------------------|-----|----| |Timestamp |Event|User| |-------------------|-----|----| |17/03/2020 03:22:00|Start|1 | |17/03/2020 03:22:05|End |1 | |17/03/2020 03:22:10|Start|2 | |17/03/2020 03:22:15|End |2 | |17/03/2020 03:23:00|Start|1 | |17/03/2020 03:23:22|End |1 | |-------------------|-----|----|
The query should return:
|-------------------|-------------------|----| |StartTimestamp |EndTimestamp |User| |-------------------|-------------------|----| |17/03/2020 03:22:00|17/03/2020 03:22:05|1 | |17/03/2020 03:22:10|17/03/2020 03:22:15|2 | |17/03/2020 03:23:00|17/03/2020 03:23:22|1 | |-------------------|-------------------|----|
You can safely assume that every 2 records is the correct pair (events are Start and End respectively, and User is the same) since the table is pre-filtered.
EDIT: Sorry, I forgot to mention that having multiple pairs for a single user is allowed. I’ve adjusted the example table above to show that.
Advertisement
Answer
As suggested, this should do what you want :
SELECT MIN(Timestamp) AS StartTimestamp, MAX(Timestamp) AS EndTimestamp, User FROM mytable GROUP BY User;
EDIT : As a user id can appear multiple times, in multiple groups, see the following query :
WITH cte AS ( SELECT mt.*, ROW_NUMBER() OVER(ORDER BY time) AS rn FROM mytable mt ) SELECT t1.userid, t1.time AS StartTimestamp, t2.time AS EndTimestamp FROM cte t1 JOIN cte t2 ON t1.rn+1 = t2.rn WHERE t1.event = 'Start'