Order rows by column whilst keeping rows with a common column together

Question

I'm running MariaDB 5.5 which is equivalent to MySQL 5.5.

I have the following test data.

CREATE TABLE `dev_test` (
    `id` INT NOT NULL AUTO_INCREMENT , 
    `date` DATE NOT NULL , 
    `venue` INT NOT NULL , 
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `dev_test` (`id`, `date`, `venue`) VALUES (NULL, '2019-08-01', '2'),
                           (NULL, '2019-09-01', '1'), (NULL, '2019-10-01', '2');
INSERT INTO `dev_test` (`id`, `date`, `venue`) VALUES (NULL, '2019-11-01', '3');

I wish to order the venues and the events so that the venue of the next event is first, then all other events at that venue. Then the next event that's not all ready listed.

So with this data I want:

Event ID 1 - 2019-08-01 Venue 2
Event ID 3 - 2019-10-01 Venue 2
Event ID 2 - 2019-09-01 Venue 1
Event ID 4 - 2019-11-01 Venue 3

I could just grab all the events in any order then order them using PHP.

Or I could select with

SELECT venue FROM `dev_test` GROUP BY venue ORDER BY date;

Then using PHP get the venues one at a time ordered by date

SELECT * FROM `dev_test` WHERE venue = 2 ORDER BY date;
SELECT * FROM `dev_test` WHERE venue = 1 ORDER BY date;

But is there a nice way in pure MySQL (MariaDB) to do this?
Maybe some way of giving all venues a temp date column that is the same as the earliest date at that venue?
Or should I just do it in PHP?

Sorry for the title gore. I tried to make "Order events by date whilst keeping events at the same location together" more generic.

Answer 1

Try this query, I think this helps you

SELECT t2.* FROM 
(SELECT venue FROM dev_test GROUP BY venue ORDER BY date) AS t1
LEFT JOIN (SELECT * FROM dev_test ORDER BY date) AS t2 ON t1.venue=t2.venue

Here result