MySQL complex ORDER BY issue

Question

I have a complicated ordering issue in my query.

Raw, Unordered Data:

+------+--------+-----------+
| id   | job_id | action_id |
+------+--------+-----------+
|    1 |      2 |         1 |  
|    2 |      2 |         2 |  
|    3 |      1 |         1 |  
|    4 |      2 |         3 |  
|    5 |      4 |         1 |  
|    6 |      1 |         2 |  
|    7 |      3 |         1 |  
|    8 |      3 |         2 |  
|    9 |      4 |         2 |  
+------+--------+-----------+

Required Ordering:

+------+--------+-----------+
| id   | job_id | action_id |
+------+--------+-----------+
|    7 |      3 |         1 |  
|    8 |      3 |         2 |  
|      |        |           |    * blank lines added for clarity,
|    5 |      4 |         1 |      not desired in actual data
|    9 |      4 |         2 |  
|      |        |           | 
|    3 |      1 |         1 |  
|    6 |      1 |         2 |  
|      |        |           | 
|    1 |      2 |         1 |  
|    2 |      2 |         2 |  
|    4 |      2 |         3 |  
+------+--------+-----------+

The theory behind this ordering:

• the largest id is the most recently added entry
• the most recent id with action_id of 1
• followed by the entries with ascending action_ids that have the same job_id
• then the next most recent action_id of 1
• ad infinitum

EDIT: I'm not able to add columns to the table in order to aid in sorting, as I've seen in some other solutions to ordering questions.

Any help is greatly appreciated!

Answer 1

My best shot is this:

SELECT * FROM tbl 
    ORDER BY FIND_IN_SET(job_id,
        (SELECT GROUP_CONCAT(job_id ORDER BY ID DESC) 
         FROM tbl WHERE action_id = 1));

Answer 2

I didn't find a way to do it easily, What do you think of the following code :

select c.id, c.job_id, c.action_id
  from (select a.id, a.job_id, a.action_id, min(b.id) as related_id
          from myTable a 
          inner join myTable b
             on a.job_id=b.job_id 
         group by a.job_id) c
 group by c.id
 order by c.related_id desc, c.action_id