title: Order rows by column whilst keeping rows with a common column together
body: <h2>I'm running MariaDB 5.5 which is equivalent to MySQL 5.5.</h2>
  <p>I have the following test data.</p>
  <pre><code>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');
  </code></pre>
  <p>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.</p>
  <p>So with this data I want:</p>
  <pre><code>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
  </code></pre>
  <p>I could just grab all the events in any order then order them using PHP.</p>
  <p>Or I could select with</p>
  <pre><code>SELECT venue FROM `dev_test` GROUP BY venue ORDER BY date;
  </code></pre>
  <p>Then using PHP get the venues one at a time ordered by date</p>
  <pre><code>SELECT * FROM `dev_test` WHERE venue = 2 ORDER BY date;
  SELECT * FROM `dev_test` WHERE venue = 1 ORDER BY date;
  </code></pre>
  <p>But is there a nice way in pure MySQL (MariaDB) to do this?<br />
  Maybe some way of giving all venues a temp date column that is the same as the earliest date at that venue?<br />
  Or should I just do it in PHP?</p>
  <p>Sorry for the title gore. I tried to make &quot;Order events by date whilst keeping events at the same location together&quot; more generic.</p>