Search code examples
mysqldatetimegroup-bymaxmin

select unique records with dates where voyage patch changes

I have a table which contains data like below (simplified)

+-----------+----------------------------+----------------------------+--------------+----------------+
| voyage_id | voyage_start_at            | voyage_end_at              | dest_city_id | depart_city_id |
+-----------+----------------------------+----------------------------+--------------+----------------+
|         3 | 2020-01-06 09:00:00.000000 | 2020-01-07 09:00:00.000000 |        21761 |           1778 |
|         2 | 2020-01-05 09:00:00.000000 | 2020-01-06 09:00:00.000000 |         1778 |           1680 |
|         2 | 2020-01-04 09:00:00.000000 | 2020-01-05 09:00:00.000000 |         1778 |           1680 |
|         2 | 2020-01-03 09:00:00.000000 | 2020-01-04 09:00:00.000000 |         1778 |           1680 |
|         2 | 2020-01-02 09:00:00.000000 | 2020-01-03 09:00:00.000000 |         1778 |           1680 |
|         1 | 2020-01-01 09:00:00.000000 | 2020-01-02 09:00:00.000000 |         1680 |           1677 |
|         1 | 2020-01-01 00:00:00.000000 | 2020-01-01 09:00:00.000000 |         1680 |           1677 |
+-----------+----------------------------+----------------------------+--------------+----------------+

The important things are:

  1. voyage_start_at date is the same like on previous voyage voyage_end_at
  2. voyage_id is the same for each row where destination and departure city are the same (voyage can be splited into several rows

What I'm want to get from is a list of voyages from city to city with dates voyage_start_at from first entry of voyage and voyage_end_at from the last entry of voyage, so this example should looks like that:

+-----------+----------------------------+----------------------------+--------------+----------------+
| voyage_id | voyage_start_at            | voyage_end_at              | dest_city_id | depart_city_id |
+-----------+----------------------------+----------------------------+--------------+----------------+
|         3 | 2020-01-06 09:00:00.000000 | 2020-01-07 09:00:00.000000 |        21761 |           1778 |
|         2 | 2020-01-02 09:00:00.000000 | 2020-01-06 09:00:00.000000 |         1778 |           1680 |
|         1 | 2020-01-01 00:00:00.000000 | 2020-01-02 09:00:00.000000 |         1680 |           1677 |
+-----------+----------------------------+----------------------------+--------------+----------------+

I believe that this can be obtained by MySQL query, so I was trying to get that by using DISTINCT, JOIN's and subquery but I didn't get it at all. Now I'm just wondering is that can be done by SQL or maybe I should get RAW data from db and convert them for my needs (I'm using PHP and TWIG).

Any helpful tips and ideas will be appreciated.

Solution

  • You can just use aggregation here:

    select
    voyage_id,
    min(voyage_start_at) voyage_start_at,
    max(voyage_end_at) voyage_end_at,
    dest_city_id,
    depart_city_id 
from mytable
group by voyage_id, dest_city_id, depart_city_id

    Demo on DB Fidddle:

    voyage_id | voyage_start_at     | voyage_end_at       | dest_city_id | depart_city_id
--------: | :------------------ | :------------------ | -----------: | -------------:
        3 | 2020-01-06 09:00:00 | 2020-01-07 09:00:00 |        21761 |           1778
        2 | 2020-01-02 09:00:00 | 2020-01-06 09:00:00 |         1778 |           1680
        1 | 2020-01-01 00:00:00 | 2020-01-02 09:00:00 |         1680 |           1677