MySQL complicated ORDER BY issue

Question

Due to changing requirements I've revisited an interface I created a couple of months ago. More features, more data. I received help with the tricky ordering requirements of the data over here. But requirements have changed, or to be more accurate, expanded. I've been tinkering for several hours now and have had no success. I'm hoping SO might be able to help unstick me once again.

Here is some simplified sample data as it appears in the database, and how I need to order it.

                **RAW**                                 **DESIRED**

╔════╦════════╦═══════════╦═══════════╗  ╔════╦════════╦═══════════╦═══════════╗
║ id ║ job_id ║ action_id ║ iteration ║  ║ id ║ job_id ║ action_id ║ iteration ║
╠════╬════════╬═══════════╬═══════════╣  ╠════╬════════╬═══════════╬═══════════╣
║  1 ║      1 ║         1 ║         0 ║  ║ 14 ║      6 ║         1 ║         0 ║
║  2 ║      1 ║         2 ║         0 ║  ║ 16 ║      6 ║         2 ║         0 ║
║  3 ║      2 ║         1 ║         0 ║  ║ 12 ║      1 ║         1 ║         1 ║
║  4 ║      3 ║         1 ║         0 ║  ║ 13 ║      1 ║         2 ║         1 ║
║  5 ║      4 ║         1 ║         0 ║  ║ 15 ║      1 ║         3 ║         1 ║
║  6 ║      3 ║         2 ║         0 ║  ║  8 ║      5 ║         1 ║         0 ║
║  7 ║      3 ║         3 ║         0 ║  ║ 10 ║      5 ║         2 ║         0 ║
║  8 ║      5 ║         1 ║         0 ║  ║ 11 ║      5 ║         3 ║         0 ║
║  9 ║      4 ║         2 ║         0 ║  ║  5 ║      4 ║         1 ║         0 ║
║ 10 ║      5 ║         2 ║         0 ║  ║  9 ║      4 ║         2 ║         0 ║
║ 11 ║      5 ║         3 ║         0 ║  ║  4 ║      3 ║         1 ║         0 ║
║ 12 ║      1 ║         1 ║         1 ║  ║  6 ║      3 ║         2 ║         0 ║
║ 13 ║      1 ║         2 ║         1 ║  ║  7 ║      3 ║         3 ║         0 ║
║ 14 ║      6 ║         1 ║         0 ║  ║  3 ║      2 ║         1 ║         0 ║
║ 15 ║      1 ║         3 ║         1 ║  ║  1 ║      1 ║         1 ║         0 ║
║ 16 ║      6 ║         2 ║         0 ║  ║  2 ║      1 ║         2 ║         0 ║
╚════╩════════╩═══════════╩═══════════╝  ╚════╩════════╩═══════════╩═══════════╝

                **EXPLAINED**

╔════╦════════╦═══════════╦═══════════╗
║ id ║ job_id ║ action_id ║ iteration ║
╠════╬════════╬═══════════╬═══════════╣     ORDERED BY:
║ 14 ║      6 ║         1 ║         0 ║
║ 16 ║      6 ║         2 ║         0 ║     The largest id with action_id of 1
╠════╬════════╬═══════════╬═══════════╣     followed by all of the rows with the
║ 12 ║      1 ║         1 ║         1 ║     same job_id and iteration number as
║ 13 ║      1 ║         2 ║         1 ║     the first, ordered by ascending
║ 15 ║      1 ║         3 ║         1 ║     action_id.
╠════╬════════╬═══════════╬═══════════╣
║  8 ║      5 ║         1 ║         0 ║     Then the next largest id with
║ 10 ║      5 ║         2 ║         0 ║     action_id = 1, etc.
║ 11 ║      5 ║         3 ║         0 ║
╠════╬════════╬═══════════╬═══════════╣
║  5 ║      4 ║         1 ║         0 ║
║  9 ║      4 ║         2 ║         0 ║
╠════╬════════╬═══════════╬═══════════╣
║  4 ║      3 ║         1 ║         0 ║
║  6 ║      3 ║         2 ║         0 ║
║  7 ║      3 ║         3 ║         0 ║
╠════╬════════╬═══════════╬═══════════╣
║  3 ║      2 ║         1 ║         0 ║
╠════╬════════╬═══════════╬═══════════╣
║  1 ║      1 ║         1 ║         0 ║
║  2 ║      1 ║         2 ║         0 ║
╚════╩════════╩═══════════╩═══════════╝

I'm currently using an ORDER BY like this:

SELECT *
FROM reports as r
ORDER BY
    FIND_IN_SET(r.job_id, ( SELECT GROUP_CONCAT(job_id ORDER BY id DESC)
                            FROM reports
                            WHERE action_id = 1)),
    r.action_id

But it doesen't take into account the iteration. I can't see where I can fit that bit of logic in. Can anybody offer any assistance?

Many thanks!

xQbert · Accepted Answer · 2014-12-12T18:14:46.870

Interesting but not that complicated.. Just need to use a self join and a subselect. Plus I think the order by is easier to read now.

http://sqlfiddle.com/#!2/7f9d2/1/0

SELECT r.id, r.job_Id, r.action_id, r.iteration,  r2.mid, r2.job_Id
FROM raw r
INNER JOIN (SELECT max(ID) mID, Job_ID, iteration 
           FROM raw 
           WHERE action_ID=1 
           GROUP BY Job_Id, iteration) r2
 on R.Job_Id = R2.Job_ID
 and R.Iteration = R2.iteration
ORDER BY r2.mid desc, iteration, r.action_Id

The key here is getting the groups you outlined together first. By doing a sub select to get the max IDs within each group and then joining those groups back to the base set, then apply the sort based on that max ID from the self join/subquery... and your other logic... you get what you're after.

Think of SQL in terms of sets this sorting gets easier.

you need a set of data which defines the groups and max value for each group (my inline view). then you need to sort based on that max value from the subset, iteration, then action from the master set.

I used a left join in case some data was messed up when i ported it to fiddle, but an inner should work just as well.

MySQL complicated ORDER BY issue

1 Answers1