SQL - Group records together-CodePudding

Currently I have a table with the following values:

  some-id   timestamp   modified_by   other_info
 --------- ----------- ------------- ------------
      1       12:45      person_1        misc
      1       12:50      person_5       stuff
      2        3:13      robot_1        things
      2        3:15      random_1       more
      3       11:33
      3       11:50

I would like a resulting SELECT to return the below:

  some-id    duration     start_person    end_person     other_info
 --------- ------------- -------------- -------------- --------------
      1         5           person_1       person_5        misc
      2         2            robot_1       random_1
      3         17

eg. The timestamp is grouped by a duplicate some_id. I want to display the duration of the some_id as well as the value of what user is linked with the respective timestamp.

I am able to create the table with duration, but trying to figure out how to include start_person and end_person

SELECT some-id, TIMESTAMP_DIFF(MAX(timestamp), MIN(timestamp), MILLISECOND) as duration
FROM t
GROUP BY some-id

CodePudding user response：

if i can't write a query, i always just use RowNumber:

    select 
      some_id,
      TIMEDIFF(MAX(timestamp), MIN(timestamp)) as duration, 
      max(IF(rownum = 1, modified_by, null)) as start_person,
      max(IF(rownum = 2, modified_by, null)) as end_person,
      max(if(rownum = 1 /*and some_id != 2*/, other_info, null)) as other_info
    from (
      select *, row_number() over(partition by some_id order by modified_by) as rownum
      from table_name
    ) t
    group by some_id

CodePudding user response：

You can find the durations and then join the original table back on to it:

with cte(id, m1, m2) as (
    select t.id, max(t.timestamp), min(t.timestamp) from tbl t group by t.id
)
select c.id, timediff(concat(date(now()), ' ', c.m1, ':00'), concat(date(now()),' ',c.m2,':00')), t1.modified_by, t2.modified_by, t1.other_info 
from cte c left join tbl t1 on c.m2 = t1.timestamp left join tbl t2 on c.m1 = t2.timestamp group by c.id, timediff(concat(date(now()), ' ', c.m1, ':00'), concat(date(now()),' ',c.m2,':00')), t1.modified_by, t2.modified_by, t1.other_info