SQL - Group records together

Question

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

Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — Stu, Dec 03 '21 at 20:38
Look at Lateral to Join based on Some-id and return the top timestamp and max timestamp descending (1 limit to 1 row only ascending and descending by timestamp joining to two lateral joins: https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql It's similar to a cross join if you're familiar with those. It's not the accepted answer but I believe it does what you're after as it processes multipe sumID's in set base https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html — xQbert, Dec 03 '21 at 20:59

score 0 · Answer 1 · answered Dec 03 '21 at 21:45

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

score 0 · Answer 2 · answered Dec 03 '21 at 21:57

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

SQL - Group records together

2 Answers2