0

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!

Matt
  • 1,377
  • 2
  • 13
  • 26

2 Answers2

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));
Justin Kiang
  • 1,270
  • 6
  • 13
  • Thanks, this worked perfectly. Bonus points for getting it all done inside the `ORDER BY` as well! – Matt Oct 17 '14 at 08:03
0

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
Stephane BOISSEAU
  • 86
  • 8