3

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
...  |  ...  | ....-..-..
| improve this question | | | | |
3

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 ;
| improve this answer | | | | |
  • Error: "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tbl.FOO'; this is incompatible with sql_mode=only_full_group_by" – Rick James Feb 1 at 20:40
  • @Rickjames I missed a GROUP BY in the first query, right? Feel free to edit. – ypercubeᵀᴹ Feb 1 at 21:57
  • "Accepted" and 3 upvotes -- I wonder what those folks did. Adding the GROUP BY will get closer to a "groupwise-min" answer, but that is not what the question is. I don't know how to fix the question or the answer. – Rick James Feb 1 at 22:02
  • Yes, it is similar to groupwise-max. But the queries (both now) work as expected and do what the question was asking. – ypercubeᵀᴹ Feb 2 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.