CODEWITHSUNDEEP
phpmysqlmariadbinnodb
Sam Dean
Sam Dean

Reputation: 404

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

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.

Upvotes: 2

Views: 98

Answers (4)

Milan Cvejic
Milan Cvejic

Reputation: 21

You can use window functions with CTEs to achieve this (note: Requires MySQL 8+:

WITH added_initial_date 
 AS (SELECT id, 
            date, 
            venue, 
            FIRST_VALUE(date) 
              OVER( 
                PARTITION BY venue 
                ORDER BY date) AS 'initial_date' 
     FROM   dev_test), 
 ranked 
 AS (SELECT id, 
            date, 
            venue, 
            initial_date, 
            RANK() 
              OVER( 
                ORDER BY initial_date ASC) AS position 
     FROM   added_initial_date) 
SELECT id, 
   date, 
   venue 
FROM   ranked 
ORDER  BY position ASC, date ASC;

Maybe it is not most effective, but it works.

UPDATE: added ordering by date to get closest record first. Here is db-fiddle: https://www.db-fiddle.com/f/puYqhDF53ocRiRys61XH53/0

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

E.g. :

SELECT a.*
  FROM dev_test a 
  LEFT 
  JOIN (SELECT * FROM dev_test ORDER BY date DESC LIMIT 1) b 
    ON b.venue = a.venue 
 ORDER 
    BY b.id IS NULL, date DESC;

This assumes dates are UNIQUE, as per the sample data

Upvotes: 0

potiev
potiev

Reputation: 586

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

enter image description here

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below - using order by venue desc, date asc

SELECT * FROM `dev_test` 
order by venue desc, date asc

Upvotes: 0

Related Questions