Mysql – Sort table by column with column ordered by earliest instance of another column

greatest-n-per-groupMySQLorder-by

Order a table query based on a column but using another column.

I have a table equivalent to the following: 

FOO  |  BAR  | TIMESTAMP
-------------------------
CAT  |  BOB  | 2018-03-01
CAT  |  ANN  | 2018-03-06
DOG  |  JON  | 2017-07-02
DOG  |  ABE  | 2012-03-02
CAT  |  HAL  | 2016-04-12
DOG  |  ANN  | 2012-02-06
BRD  |  JON  | 2015-09-12
...  |  ...  | ....-..-..

I want it to return a list ordered by FOO, but I don't want it ordered ascending or descending, but by whichever has the earliest timestamp. So if
the earliest Bird was in 2015, the earliest dog was in 2012, and the earliest cat in 2016, I'd want all the values ordered by DOG then BRD then CAT, and then afterwards sort by BAR.

However, I have no clue how to do this. I obviously don't want to order by timestamp first, or the dogs, cats, and birds will be scattered throughout, and ordering by timestamp after Foo wil leave it ordered BRD, CAT, DOG instead of DOG, BRD, CAT.

So output would need to kind of look like this:

FOO  |  BAR  | TIMESTAMP
-------------------------
DOG  |  ABE  | 2012-03-02
DOG  |  ANN  | 2012-02-06
DOG  |  JON  | 2017-07-02
BRD  |  JON  | 2015-09-12
CAT  |  ANN  | 2018-03-06
CAT  |  BOB  | 2018-03-01
CAT  |  HAL  | 2016-04-12
...  |  ...  | ....-..-..

Best Answer

You can use a subquery / derived table to get the earliest timestamps and then join;

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
  JOIN
    ( SELECT foo, MIN(timestamp) AS min_timestamp
      FROM tbl
      GROUP BY foo
    ) AS m
  ON  t.foo = m.foo
ORDER BY
    m.min_timestamp,
    t.foo,
    t.bar ;

In MariaDB (and in MySQL version 8, when it is released), you can also use window functions:

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
ORDER BY
    MIN(t.timestamp) OVER (PARTITION BY t.foo),
    t.foo,
    t.bar ;
Related Question