Skip to content
Advertisement

Selecting first and last time stamps of a section

I have a MySQL database with a table like:

CREATE TABLE example (Batch_Num int, Time_Stamp datetime);
INSERT INTO example VALUES
    (1, '2020-12-10 16:37:43'),
    (1, '2020-12-11 09:47:31'),
    (1, '2020-12-11 14:02:17'),
    (1, '2020-12-11 15:28:02'),
    (2, '2020-12-12 15:08:52'),
    (2, '2020-12-14 10:38:02'),
    (2, '2020-12-14 16:22:35'),
    (2, '2020-12-15 08:44:13'),
    (3, '2020-12-16 11:38:05'),
    (3, '2020-12-17 10:19:13'),
    (3, '2020-12-17 14:45:28');

+-----------+-----------------------+
| Batch_Num |      Time_Stamp       |
+-----------+-----------------------+
|         1 | '2020-12-10 16:37:43' |
|         1 | '2020-12-11 09:47:31' |
|         1 | '2020-12-11 14:02:17' |
|         1 | '2020-12-11 15:28:02' |
|         2 | '2020-12-12 15:08:52' |
|         2 | '2020-12-14 10:38:02' |
|         2 | '2020-12-14 16:22:35' |
|         2 | '2020-12-15 08:44:13' |
|         3 | '2020-12-16 11:38:05' |
|         3 | '2020-12-17 10:19:13' |
|         3 | '2020-12-17 14:45:28' |
+-----------+-----------------------+

I would like to select from this table the first and last timestamp for each value of each Batch_Number. I would like the table to look like:

+-----------+-----------------------+-----------------------+
| Batch_Num | Beginning_Time_Stamp  |    End_Time_Stamp     |
+-----------+-----------------------+-----------------------+
|         1 | '2020-12-10 16:37:43' | '2020-12-11 15:28:02' |
|         2 | '2020-12-12 15:08:52' | '2020-12-15 08:44:13' |
|         3 | '2020-12-16 11:38:05' | '2020-12-17 14:45:28' |
+-----------+-----------------------+-----------------------+

I am not sure how to select both, when the previous Batch_Num is different from the curent one, and also when the next one is different.

Answer

A basic GROUP BY query should work here:

SELECT
    Batch_Num,
    MIN(Time_Stamp) AS Beginning_Time_Stamp,
    MAX(Time_Stamp) AS End_Time_Stamp
FROM example
GROUP BY
    Batch_Num
ORDER BY
    Batch_Num;

screen capture from demo link below

Demo