Retrieve MIN and MAX date in one query

Question

I have in a mysql database a table "travel_data" with three relevant columns:

+--------------------------+
| from  | to   | date      |
+--------------------------+
|City1 | City2 | 2015-12-12|
|City3 | City4 | 2016-12-12|
|City1 | City2 | 2015-06-06|
|City3 | City4 | 2017-01-01|
+--------------------------+

(plus a few other colums that I don't need at the moment).

I'm looking for a query that allows me to get, in one query if possible, the first and last date for each itinerary per row, i.e. something like:

+------------------------------------------------+
|from  | to    | firstTravelDate | LastTravelDate|
+------------------------------------------------+
|City1 | City2 | 2015-06-06      | 2015-12-12    |
|City3 | City4 | 2016-12-12      | 2017-01-01    |
+------------------------------------------------+

I've search for "mysql select maximum minimum in one query" but solutions from there, inasfar as they worked at all, returned mix/max per table; I need the min/max per possible combination of from/to. This has me baffled to an extent where I haven't even a start for a working query. This needs to run only on MySql, so a standards-compliant solution is not necessary.

(Improvements on the title are welcome, I don't feel the title fully covers the actual question).

Answer 1

As Gordon indicated in the comments, this is a simple aggregation query. You will want to do the following

SELECT `from`, to, 
       MIN(date) AS firstTravelDate, 
       MAX(date) AS LastTravelDate 
FROM travel_data 
GROUP BY `from`, to;

You may need back ticks on the variables "FROM" and "TO" since those are reserved keywords in MySQL.

Also, in the interest of "teaching you to fish" as it were, I would recommend taking a look at https://sqlschool.modeanalytics.com/. There are a lot of SQL resources out there, and Mode's focuses on PostgreSQL not MySQL... but it's also very good and very fun to read.

Answer 2

Try;

select
   `from`, `to`, min(date) firstTravelDate, max(date) LastTravelDate
from travel_data
group by `from`, `to`

Demo sqlfiddle